Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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:
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".
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
If statement in macro | Excel Discussion (Misc queries) | |||
If statement in macro | Excel Discussion (Misc queries) | |||
can i use an IF Statement to run a macro? | Excel Worksheet Functions | |||
If statement in a Macro | Excel Discussion (Misc queries) | |||
if statement in Macro | Excel Worksheet Functions |