asked on

IF statement VBA

Hi,

I need a line of code that looks at tab "XYZ"

It needs to check AF8:AF19 and find "Reporting Month"

eg If AE14 = "Reporting Month", it then must offset two columns, therefore finding "AH14", iF AH14= "=G8", then it does not call the sub "DoWork"

If AE14 = "Reporting Month" and AH14 is blank, then call the sub "DoWork"

Hope this makes sense!

Thanks

Rgonzo1971

Hi,

pls try

Sub macro()

intOffset = WorksheetFunction.Match("Reporting Month", Sheets("XYZ").Range("AF8:AF19")) - 1
If Range("AF" & 8 + intOffset).Formula = "" Then
Call DoWork
End If
End Sub

Regards

Seamus2626

ASKER

Thanks Rgonzo,

So my reporting month is July (AE14) and the cells AH14 are blank, but the sub is not being called.

So the statement needs to be

Find Reporting month in range and then check two columns to right, if no formula, call sub, if formula, skip

Thanks

Rgonzo1971

pls try

Sub macro()

intOffset = WorksheetFunction.Match("Reporting Month", Sheets("XYZ").Range("AE8:AE19")) - 1
If Range("AH" & 8 + intOffset).Formula = "" Then
Call DoWork
End If
End Sub

Seamus2626

ASKER

Here is my sub Rgonzo

So currently i have formula of =G8 in AH14 & AE14 = "Reporting Month"

The code is going to run Call BrDeck4 - That is the condition where it does not run the sub

The condition it runs the sub is

Where cell in range (AE8:AE19) = "Reporting Month", look at the cell in the corresponding row in AH and if there is no formula, call the sub

Thanks

intOffset = WorksheetFunction.Match("Reporting Month", Sheets("ASP New BR Deck 4").Range("AF8:AF19")) - 1
If Range("AH" & 8 + intOffset).Formula = "" Then
Call BrDeck4
End If

Rgonzo1971

On your last code

I've replaced AF with AE

intOffset = WorksheetFunction.Match("Reporting Month", Sheets("ASP New BR Deck 4").Range("AE8:AE19")) - 1
If Range("AH" & 8 + intOffset).Formula = "" Then
Call BrDeck4
End If

There is a bit of confusion due to your question where you mention AF

Seamus2626

ASKER

Hi Rgonzo, i have attached my ss with the data im working on

When you run the sub ( i put in module 1) it will call the sub BrDeck4 and display the msg box

This is incorrect as the corresponding column to Reporting Month is NOT blank, so the sub shouldnt run

Thanks

Seamus2626

ASKER

File

Seamus2626

ASKER

File again!
Book1.xlsm

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

Perfect, thanks!!