If "#NA" is in Range, Do this

HobbesNYC

New Member
Joined
Aug 8, 2012
Messages
13
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!!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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

New Member
Joined
Aug 8, 2012
Messages
13
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
How about
Code:
If (Application.Sum(Range("N5:N30")) = CVErr(xlErrNA)) Then
    ' blue code
End If
 

HobbesNYC

New Member
Joined
Aug 8, 2012
Messages
13

ADVERTISEMENT

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Aug 8, 2012
Messages
13

ADVERTISEMENT

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,917
Messages
5,525,621
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top