referencing a cell value that is populated from a vlookup

[Blanchetdb]

Hi

I require to reference a cell value using an IF statement. The issue is that the cell value is populated from a vlookup and when I try the IF statement, the desired result does not happen

this is the VLookup : =IFERROR(VLOOKUP(IND_LOO_Builder_ENG!L9,Position_List_ENG!2:10004,18,FALSE),"") which is located in cell AI6 and populates a value



the IF statement in VBA is

Private Sub Worksheet_Change(ByVal Target As Range) ' Form Manipulation / Hide and Unhide rows
 

'Hours of Work
If Range("AI6") = "PE" Then
Worksheets("IND_LOO_Builder_ENG").Range("36:42").EntireRow.Hidden = True
 Else
Worksheets("IND_LOO_Builder_ENG").Range("36:42").EntireRow.Hidden = False
End If

End sub

The process does not seem to recognize the value "PE" in cell AI6 but if I type the value PE in the cell without using the vlookup to populate, the process works

 

[Fluff]

The change event is not triggered by formulae, as the cell has not changed.
You could use the calculate event, but that will trigger whenever any cell on the sheet is recalculated.

 

[Blanchetdb]

The change event is not triggered by formulae, as the cell has not changed.
You could use the calculate event, but that will trigger whenever any cell on the sheet is recalculated.

thank you for responding....

unfortunately I have absolutely no idea what you mean as I am a rookie when it comes to VBA. I am able to reference a cell value when the value is typed into the cell (If I type PE in a cell and reference that cell....then the process works) but when the cell is populated from a formula.... nothing happens

am I missing something in my IF statement or should a refer to the cell other than Range("AI6")?

 

[Fluff]

The code you are using is a change event & that does not work with formulae.
You can use

Private Sub Worksheet_Calculate()
'Hours of Work
If Range("AI6") = "PE" Then
Worksheets("IND_LOO_Builder_ENG").Range("36:42").EntireRow.Hidden = True
 Else
Worksheets("IND_LOO_Builder_ENG").Range("36:42").EntireRow.Hidden = False
End If
End Sub

But it could bring you sheet to a grinding halt, as it will run whenever any formula on that sheet calculates.

 

[Blanchetdb]

thank you ….. learnt something new today

much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.