PDA

View Full Version : Solved: Find



xlUser
07-12-2011, 03:58 AM
Hi,

I have the code below that uses the find function to search for the occurance of the word "DIff" in a row and when it finds it, displays a message box showing what column it was found in and the value in the first row of that column.

This works fine where "Diff" occurs but where it does not exist the macro falls over with a run time error 91 : Object variable or with block variable not set.

In the event that "DIFF" does not exist I want to macro to carry on with the rest of the code, i.e call the procedure part2


Sub Testing()
Rows("5:5").Select
Selection.Find(What:="diff", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

x = ActiveCell.Column
z = Cells(1, x).Value

MsgBox "Column" & x & ": - " & z

Call part2

End Sub



Would be great if someone could advise of changes needed.

Thanks,
H User

mohanvijay
07-12-2011, 07:37 AM
Try this

Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("5:5").Find("diff")
If Not rng Is Nothing Then
x = rng.Column
z = Cells(1, x).Value
MsgBox "Column" & x & ": - " & z
End If

Call part2

xlUser
07-15-2011, 10:40 AM
This works great,

Thanks.