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
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0
Seamus2626Author 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
0
Rgonzo1971Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Seamus2626Author 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
0
Rgonzo1971Commented:
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
0
Seamus2626Author 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
0
Seamus2626Author Commented:
File
0
Seamus2626Author Commented:
File again!
Book1.xlsm
0
Rgonzo1971Commented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Perfect, thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.