Searching for a value in another sheet

  • Thread starter Thread starter serge
  • Start date Start date
S

serge

Hi,

I'm using the formula below to search for a value that i've entered i
the cell E3 of sheet3. It has to find the same value in column A o
sheet1. But i get a error 1004 at the line
Sheets("sheet1").Range("a:a").Cells.Find(what:=scannumber, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate

"Method activate of this range has failed".
Can someone tell me what is wrong?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim scannumber As Variant
Dim teller As Variant
Dim timestamp As Date
Dim myrange As Range

Set myrange = Intersect(Target, Range("E:E"))
If Not myrange Is Nothing Then
Sheets("sheet3").Select
scannumber = Range("E3")
If scannumber = "" Then
End If
If scannumber <> "" Then
Sheets("sheet1").Range("a:a").Cells.Find(what:=scannumber, _
LookIn:=xlFormulas, _
MatchCase:=False).Activate
..
 
More than likely, the find command has failed to find scannumber in column A
of sheet1. The activate on the end of the command tries to activate
"nothing" and this causes the error.

A way to avoid the error is:
Dim rng as Range
Set rng = Sheets("sheet1").Range("a:a").Cells.Find(what:=scannumber, _
LookIn:=xlFormulas, _
MatchCase:=False)
if not rng is nothing then
rng.activate
else
msgbox scannumber & " was not found on Sheet1, column A"
End if
 
Back
Top