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

 

[RoryA]

If that's an error value, not text, you can use:

If Range("AF1").Value = CVErr(xlerrna) Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
     End If

Note that that test has to be before the other two.

 

[jimmisavage]

Thanks Rory,
I think I've gone wrong somewhere now. Your code works fine and fixed the issue I had but now i get an error on the next If Range (Non-Commercial):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("AF1") = CVErr(xlErrNA) Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
    End If
    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
    End Sub

 

[footoo]

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

 

[jimmisavage]

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

Hi Footoo, I'm still getting the error using this code. :(

 

[jimmisavage]

This seems to have worked though. Thanks for the help guys!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("AF1") = CVErr(xlErrNA) Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
    Else
    If Range("AF1") = "Non Commercial" Then
        Columns("P").EntireColumn.Hidden = True
        Columns("Q").EntireColumn.Hidden = True
        Columns("R").EntireColumn.Hidden = True
    Else
    If Range("AF1") = "Commercial" Then
        Columns("P").EntireColumn.Hidden = False
        Columns("Q").EntireColumn.Hidden = False
        Columns("R").EntireColumn.Hidden = False
    End If
    End If
    End If
    End Sub

 

[RoryA]

You need to nest them using ElseIf:

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

 

[jimmisavage]

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

 

[footoo]

Would it not be better to change your formula so that it returns "No Match" (say) instead of returning an error?
=IFERROR(your formula, "No Match")

Then you should be able to use the code per post #4

 

[jimmisavage]

Would it not be better to change your formula so that it returns "No Match" (say) instead of returning an error?
=IFERROR(your formula, "No Match")

Then you should be able to use the code per post #4

I can certainly try that but I can't make the code out of VBA. I know this is complicated but how to i convert this into a VBA formula?

=ISERROR(VLOOKUP(AE1,'Study Database'!B8:AN1001,9,0),"No Match")

Something like this?

"=ISERROR(VLOOKUP(RC[-1],'Study Database'!R[7]C[-30]:R[1000]C[8],9,0),"No Match")"

It's not working if that is correct.