Cells.Find problem

[damezumari]

I use Excel 2000 and Win2000.

I have two sheets in my workbook. One of the cells on the sheet called
"dummy" has the word "charge" in it.

This code in the click event for a button on the "dummy" sheet, finds
the cell with "charge":

Private Sub CommandButton1_Click()
Dim FoundCell As Range
Set FoundCell = Cells.Find(What:="charge", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub

However, this code in the click event for a button on my other sheet,
does not:

Private Sub CommandButton1_Click()
Dim FoundCell As Range
Sheets("dummy").Select ' only change from the code above
Set FoundCell = Cells.Find(What:="charge", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub

What am I doing wrong?

 

[Rowan Drummond]

Because this code is in a worksheet code module if you do not explicitly
qualify a reference it is assumed to be refering to the sheet that holds
the code. So even though you have selected the Dummy sheet the line
Set FoundCell = Cells.Find(What:="charge", .... is still searching the
sheet that has your button.

Try:
Private Sub CommandButton1_Click()
Dim FoundCell As Range
With Sheets("dummy")
Set FoundCell = .Cells.Find(What:="charge", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
MsgBox "Found"
End If
End Sub

Hope this helps
Rowan

 

[damezumari]

Thanks Rowan! It worked like a dream!

 

[Rowan Drummond]

You're welcome.