If "#NA" is in Range, Do this

[HobbesNYC]

Hi All,

I'm trying to make an if. I want it to search a range (N5-N30) and if the value "N/A" is in any cell, to click on that cell, move 8 over, then run the blue code (the blue code already works). Any ideas how to make the If, Then, Search, Offset, Select work?


If ActiveSheet.Range("N5:N30").Value = "#N/A" Then
Selection.Offset(0, 8).Select
Windows(t).Activate ' Activate Book2
ActiveSheet.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:="="
Columns("BS").Select
Selection.End(xlDown).Select
Selection.Copy
Windows(s).Activate 'Activate Book1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
End If


Thank you!!

 

[Joe4]

If the "#N/A" is an error, and not hard-coded text, you cannot check for the value being equal to "#N/A".
However, you should be able to use:

If Application.WorksheetFunction.IsNA(...

I think you will need to loop through each cell individually though, I don't think you can check the whole range at once.
Try it and see how far you get and post back if you run into trouble.

 

[HobbesNYC]

I tried setting it up like this using the InStr function, I'm getting a type mismatch error. The bold part is what I made and can't get to work, the rest already does.

Dim FindNA As String
FindNA = InStr(1, Range("N5:N30"), "#N/A")
If FindNA > 0 Then Range(FindNA).Select
Selection.Offset(0, 8).Select
Windows(t).Activate ' Activate Book2
ActiveSheet.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:="="
Columns("BS").Select
Selection.End(xlDown).Select
Selection.Copy
Windows(s).Activate 'Activate Book1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[mikerickson]

How about

If (Application.Sum(Range("N5:N30")) = CVErr(xlErrNA)) Then
    ' blue code
End If

 

[HobbesNYC]

It isn't an error, the text is actually #NA, and how would I select it after doing that?

 

[Jonmo1]

I tried setting it up like this using the InStr function, I'm getting a type mismatch error. The bold part is what I made and can't get to work, the rest already does.

Dim FindNA As String
FindNA = InStr(1, Range("N5:N30"), "#N/A")
If FindNA > 0 Then Range(FindNA).Select
Selection.Offset(0, 8).Select
Windows(t).Activate ' Activate Book2
ActiveSheet.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:="="
Columns("BS").Select
Selection.End(xlDown).Select
Selection.Copy
Windows(s).Activate 'Activate Book1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It isn't an error, the text is actually #NA, and how would I select it after doing that?

If the text is #NA, then why are you searching for #N/A ?

 

[HobbesNYC]

sorry, i meant to say the text is #N/A.

 

[Jonmo1]

Pick a cell that contains #N/A, say N10 for example
What does this formula return

=ISERROR(N10)

Change N10 to a cell that actually contains one of the text strings #N/A

 

[HobbesNYC]

A27 is, it says True

 

[Jonmo1]

A27 is, it says True

Then they ARE errors, not just text strings.

And you have to loop through each cell in the range, and test it for the error as post#2 suggested.