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]

So when I tried to use the code again later in the same code when working on different sheets, (I just copied and pasted in and changed the sheet names), it didn't work. They are the exact same format and what not, do I have to clear c or something?

 

[Jonmo1]

It's all the selecting and activating, then depending on what is currently selected or activated.

You're best off explicitly naming books and sheets.

Try this

Dim bk1 As Worksheet, bk2 As Worksheet, c As Range

'This is Book/Sheet you are searching for errors
Set bk1 = Workbooks("Book1.xls").Sheets("Sheet1")

'This is the Book/Sheet you are filtering and Copying from
Set bk2 = Workbooks("Book2.xls").Sheets("Sheet1")

For Each c In bk1.Range("A5:A30")
    If IsError(c) Then
        bk2.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:="="
        bk2.Range("BS1").End(xlDown).Copy
        c.Offset(0, 8).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Exit For
    End If
Next c

And adjust book and sheet names accordingly.