Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 132
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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(01).Value "GOOD"
            
    End If
            
    ActiveCell.Offset(10).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".
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 132
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Don Don is offline
external usenet poster
 
Posts: 487
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If statement in macro orquidea Excel Discussion (Misc queries) 2 May 11th 08 08:19 PM
If statement in macro punter Excel Discussion (Misc queries) 3 June 20th 06 12:23 PM
can i use an IF Statement to run a macro? Davidrowland88 Excel Worksheet Functions 2 February 11th 05 01:45 PM
If statement in a Macro Dave Excel Discussion (Misc queries) 1 January 29th 05 01:17 AM
if statement in Macro John Excel Worksheet Functions 6 December 29th 04 08:17 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"