IF statement VBA

Seamus2626
Seamus2626 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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

Author

Commented:
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
Top Expert 2016

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Top Expert 2016

Commented:
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

Author

Commented:
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

Author

Commented:
File

Author

Commented:
File again!
Book1.xlsm
Top Expert 2016
Commented:
Corrected code

Forgot the exact match

Sub macro4()

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

Author

Commented:
Perfect, thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial