I need to lookup the values in column A with columns B to F, one at a
time. I put in a code which looks up each value in each column and
highlights it in yellow. It works great when it finds the value.
However, the problem lies when it doesnt find the value in the column.
I get a runtime error - error 91 saying "Object variable or with
variable not set". The code is given below:

Dim lastrow As Long, i As Integer, x As Integer, loc As Variant,
searchtext As Variant

Public Sub validate_names()

Sheets("Buyer Names").Select
Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

loc = ""

For i = 2 To lastrow

For x = 2 To 6

searchtext = Range("A" & i).Text
Range(searchtext).Select

Selection.Copy

Columns(x).Select

With Selection.Find(What:=searchtext, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

loc = ActiveCell.Address

If loc < "" Then

Range(loc).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

End If

End With

Next

Range("A" & i).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Next

End Sub

Please help me. Your help is greatly appreciated.

set c = Selection.Find(What:=searchtext, After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
if not c is nothing then
With c.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
else
msgbox ("did not find " & searchtext)
end if




"Arlette" wrote:

I need to lookup the values in column A with columns B to F, one at a
time. I put in a code which looks up each value in each column and
highlights it in yellow. It works great when it finds the value.
However, the problem lies when it doesnt find the value in the column.
I get a runtime error - error 91 saying "Object variable or with
variable not set". The code is given below:

Dim lastrow As Long, i As Integer, x As Integer, loc As Variant,
searchtext As Variant

Public Sub validate_names()

Sheets("Buyer Names").Select
Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

loc = ""

For i = 2 To lastrow

For x = 2 To 6

searchtext = Range("A" & i).Text
Range(searchtext).Select

Selection.Copy

Columns(x).Select

With Selection.Find(What:=searchtext, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

loc = ActiveCell.Address

If loc < "" Then

Range(loc).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

End If

End With

Next

Range("A" & i).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Next

End Sub

Please help me. Your help is greatly appreciated.

On Aug 18, 3:06*pm, Joel wrote:
* * * * set c = *Selection.Find(What:=searchtext, After:=ActiveCell, _
* * * * * *LookIn:=xlFormulas, _
* * * * * *LookAt:=xlPart, SearchOrder:=xlByRows,
* * * * * *SearchDirection:=xlNext, _
* * * * * *MatchCase:=False, SearchFormat:=False)
* * * * if not c is nothing then
* * * * * * * * With c.Interior
* * * * * * * * * * .ColorIndex = 36
* * * * * * * * * * .Pattern = xlSolid
* * * * * * * * End With
* * * * *else
* * * * * * msgbox ("did not find " & searchtext)
* * * * *end if



"Arlette" wrote:
I need to lookup the values in column A with columns B to F, one at a
time. *I put in a code which looks up each value in each column and
highlights it in yellow. *It works great when it finds the value.
However, the problem lies when it doesnt find the value in the column.
I get a runtime error - error 91 saying "Object variable or with
variable not set". The code is given below:

Dim lastrow As Long, i As Integer, x As Integer, loc As Variant,
searchtext As Variant

Public Sub validate_names()

Sheets("Buyer Names").Select
Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

loc = ""

For i = 2 To lastrow

* * *For x = 2 To 6

* * * * searchtext = Range("A" & i).Text
* * * * Range(searchtext).Select

* * * * Selection.Copy

* * * * Columns(x).Select

* * * * With Selection.Find(What:=searchtext, After:=ActiveCell,
LookIn:=xlFormulas, _
* * * * * * LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
* * * * * * MatchCase:=False, SearchFormat:=False).Activate

* * * * * * loc = ActiveCell.Address

* * * * * * If loc < "" Then

* * * * * * * * Range(loc).Select
* * * * * * * * With Selection.Interior
* * * * * * * * * * .ColorIndex = 36
* * * * * * * * * * .Pattern = xlSolid
* * * * * * * * End With

* * * * * * End If

* * * * *End With

* * Next

* * * * Range("A" & i).Select
* * * * With Selection.Interior
* * * * * * .ColorIndex = 36
* * * * * * .Pattern = xlSolid
* * * * End With

Next

End Sub

Please help me. *Your help is greatly appreciated.- Hide quoted text -

- Show quoted text -

Thank you so much...it really helped...