Macro if and / or statement
 

Hi:

I am having difficulty writing a macro with the if and /or together.

Column C will be = "GOOD" if B is different than "VOY" and A is iqual to
London and Cambridge.

A B C
London VOY
Cambridege GOOD
London GOOD

I have the below string but it is just evaluating the second part of the
string
Do (the and conditions)
If ActiveCell.Offset(0, -1) < "VOY" And ActiveCell.Offset(0, -2) = "
London" Or ActiveCell.Offset(0, -2) = "Cambridge" Then
ActiveCell.Value = "GOOD"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, -13) = ""

Could anyone help me please.

Thanks,
Orquidea

Answer: Macro if and / or statement
 

Hi Orquidea,

It looks like you're trying to write a macro that will evaluate two conditions using the if statement and the and / or operators. Here's an example of how you can do this:

1. First, select the cell where you want to start the macro.
2. Open the VBA editor by pressing Alt + F11.
3. In the editor, create a new subroutine by clicking on "Insert" and then "Module".
4. Type the following code:
   
   
   Formula:

   ---

   Sub EvaluateConditions()
    Do Until ActiveCell.Offset(0, -13) = ""
        If ActiveCell.Offset(0, -1) < "VOY" And (ActiveCell.Offset(0, -2) = "London" Or ActiveCell.Offset(0, -2) = "Cambridge") Then
            ActiveCell.Offset(0, 1).Value = "GOOD"
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub 


   ---

5. Save the macro and close the editor.
6. Go back to your Excel sheet and run the macro by pressing Alt + F8 and selecting "EvaluateConditions" from the list.

This macro will loop through all the cells in column C and evaluate the two conditions you specified: B is different than "VOY" and A is equal to "London" or "Cambridge". If both conditions are true, the macro will set the value of the cell in column C to "GOOD".

Macro if and / or statement
 

Try the following code, which sets the active cell as range, captures the
values from the 2 columns to its left and evaluates the 2 values against your
critieria. If there's a match you get the answer of GOOD in the active cell,
otherwise you get absolutely nothing in the cell.:

Sub Good()

Dim val1 As Variant
Dim val2 As Variant
Dim r As Range

Set r = Application.ActiveCell
val1 = r.Offset(, -1).Value
val2 = r.Offset(, -2).Value

If val1 < "VOY" And val2 = "Cambridge" Or _
val2 = "London" Then
r.Value = "Good"
End If

Set r = Nothing

End Sub

--
Kevin Backmann


"orquidea" wrote:

Hi:

I am having difficulty writing a macro with the if and /or together.

Column C will be = "GOOD" if B is different than "VOY" and A is iqual to
London and Cambridge.

A B C
London VOY
Cambridege GOOD
London GOOD

I have the below string but it is just evaluating the second part of the
string
Do (the and conditions)
If ActiveCell.Offset(0, -1) < "VOY" And ActiveCell.Offset(0, -2) = "
London" Or ActiveCell.Offset(0, -2) = "Cambridge" Then
ActiveCell.Value = "GOOD"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, -13) = ""

Could anyone help me please.

Thanks,
Orquidea

Macro if and / or statement
 

Thanks a lot

"Kevin B" wrote:

Try the following code, which sets the active cell as range, captures the
values from the 2 columns to its left and evaluates the 2 values against your
critieria. If there's a match you get the answer of GOOD in the active cell,
otherwise you get absolutely nothing in the cell.:

Sub Good()

Dim val1 As Variant
Dim val2 As Variant
Dim r As Range

Set r = Application.ActiveCell
val1 = r.Offset(, -1).Value
val2 = r.Offset(, -2).Value

If val1 < "VOY" And val2 = "Cambridge" Or _
val2 = "London" Then
r.Value = "Good"
End If

Set r = Nothing

End Sub

--
Kevin Backmann


"orquidea" wrote:

Hi:

I am having difficulty writing a macro with the if and /or together.

Column C will be = "GOOD" if B is different than "VOY" and A is iqual to
London and Cambridge.

A B C
London VOY
Cambridege GOOD
London GOOD

I have the below string but it is just evaluating the second part of the
string
Do (the and conditions)
If ActiveCell.Offset(0, -1) < "VOY" And ActiveCell.Offset(0, -2) = "
London" Or ActiveCell.Offset(0, -2) = "Cambridge" Then
ActiveCell.Value = "GOOD"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, -13) = ""

Could anyone help me please.

Thanks,
Orquidea

Macro if and / or statement
 

why not try
=IF(AND(OR(A1="London",A1="Cambridege"),B1<"VOY") ,"good","")

"orquidea" wrote:

Hi:

I am having difficulty writing a macro with the if and /or together.

Column C will be = "GOOD" if B is different than "VOY" and A is iqual to
London and Cambridge.

A B C
London VOY
Cambridege GOOD
London GOOD

I have the below string but it is just evaluating the second part of the
string
Do (the and conditions)
If ActiveCell.Offset(0, -1) < "VOY" And ActiveCell.Offset(0, -2) = "
London" Or ActiveCell.Offset(0, -2) = "Cambridge" Then
ActiveCell.Value = "GOOD"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset(0, -13) = ""

Could anyone help me please.

Thanks,
Orquidea