Problems, doing a find in another sheet.

[Jan Kronsell]

I have a commandbutton in a sheet, with the following code

Private Sub CommandButton1_Click()
Dim nv As String

nv = Range("a2").Value
Sheets("data").Select

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


End Sub

When I use the code I get a runtime error 1004: The method Activeate for the
class Range did not succeed (its in Danish so im not sure of the
translation), and it is the Cells.Find that fails.

If I do the find in the same sheet as the button, it works allright. How can
I get it to perform a seach in another sheet than contains the command
button.

Jan

 

[Jan Kronsell]

Additionally:

If I change my code to only contain

Sheets("data").Range("a1").Activate

I get the same error, so it seems my problem is how to activate a cell in
one sheet, from code on a button in another.

Jan

 

[Jan Kronsell]

I discovered that even if Ii selected sheet first I had to refer to the
sheet in the Range statement as well.

Jan

 

[money]

Try this out.

Private Sub CommandButton1_Click()
Dim nv As String

nv = Range("a2").Value
Sheets("data").Select

Cells.Find(What:=nv, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False) _
.Activate
End Sub