• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 6
  • 4
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Seamus2626Author Commented:
Perfect, thanks!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now