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!!

 

[HobbesNYC]

Can you tell me how to get started with that? I don't know how to use that function and I can't find it online. I'm literally 4 days into using VBA.

 

[Jonmo1]

Try

For Each c In Range("N5:N30")
    If IsError(c) Then
        '....do some stuff

       Exit For
    End if
Next c

Although this will capture ANY error, not just #N/A specifically.

 

[mikerickson]

You can avoid looping with

If (Application.Sum(Range"N5:N30") = CVErr(xlErrNa)) Then

 

[Jonmo1]

You can avoid looping with

If (Application.Sum(Range"N5:N30") = CVErr(xlErrNa)) Then

Unless there are OTHER errors in the range, AND the first error in the range is something other than #N/A.

Of coarse my code has the same problem..


To specifically look for #N/A, you would have loop and use
Application.ISNA(...) like in post#2

something like

For Each c In Range("A1:A10")
    If Application.IsNA(c) Then
        'Do some stuff

        Exit For
    End If
Next c

 

[HobbesNYC]

So this kinda worked. It searched for the error but it didn't select it. It just pasted into the cell it was already in:

For Each c In Range("A5:A30")
If IsError(c) Then
Selection.Offset(0, 0).Select 'For now, later I will change it to 8 over
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
Exit For
End If
Next c

 

[EvR]

or:

if Evaluate("SUM(IF(ISNA(N5:N30),1))") >0

 

[Jonmo1]

c.Offset(0,8).Select

 

[HobbesNYC]

You are a god

 

[HobbesNYC]

I really appreciate the help, works great

 

[Jonmo1]

Glad to help, thanks for the feedback.