search column for data

[Daniel M]

I have a macro that has a vlookup formula from a linked spreadsheet. After
calculation i paste special values. If the data is not found it puts a #N/A
in column B of my spreadsheet. I want to search column B for value #N/A and
if i find any instances, jump out of the macro and throw up a message box.
can someone help me with this? Also i do not know how long column B will be.
so i should probably start at the last cell in column B that contains data.
Thanks.

 

[Gleam]

Please try this:

Sub test()
Dim Result As Range
Columns("B:B").Select
Set Result = Selection.Find(What:="#N/A", After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:= False, SearchFormat:=False)
If Not Result Is Nothing Then
MsgBox "Found #N/A. Exiting."
Exit Sub
End If
End Sub

 

[Nigel]

Test for any errors in column B using (set references as required).....

Sub ErrorsInColumn()
Dim myRng As Range, myCell As Range, mySht As Worksheet

Set mySht = Sheets("Sheet1")

With mySht
Set myRng = .Range(.Cells(1, 2), .Cells(.Cells(.Rows.Count,
2).End(xlUp).Row, 2))
For Each myCell In myRng
If IsError(myCell.Value) Then MsgBox "Error in Column B"
Next myCell
End With

End Sub