If Range("AF1") = "#N/A" Then

[jimmisavage]

I have a simple VBA to hide and unhide cells based in a formula in AF1

If Range("AF1") = "Non Commercial" Then
        Columns("P").EntireColumn.Hidden = True
        Columns("Q").EntireColumn.Hidden = True
        Columns("R").EntireColumn.Hidden = True
    End If
If Range("AF1") = "Commercial" Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
    End If

This does the job but there is also an instance when AF1 can be #N/A which throws an error because i've not told it that would be an option.

I've tried adding an option of below but it doesn't seem to recognise the #N/A

If Range("AF1") = "#N/A" Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
     End If

How would I combat this?

Many thanks
Jimmi

 

[footoo]

IFERROR (not ISERROR)

The VBA code can be :

Range("AF1").Formula="=IFERROR(VLOOKUP(AE1,'Study Database'!B8:AN1001,9,0),""No Match"")"

 

[RoryA]

I spoke too soon! Now this only works if AF1 is #N/A. If it's not it won't move on to check if it's non-commercial or commercial

I don't see how that could be for the code in post #7 .

 

[jimmisavage]

Ignore me, it's working just fine now Footoo
Thank you both for the help!