VBA Find produces 1004 error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this code :-

Private Sub CommandButton1_Click()

Dim FW As Variant
Dim rngCell As Range

FW = Worksheets("LU").Cells(1, 1).Value

With Worksheets("Detail")
Set rngCell = .Cells.Find(What:=FW, _
After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With

If Not rngCell Is Nothing Then
MsgBox rngCell.Row
End If

End Sub

Worksheets("LU").Cells(1, 1) contains value "109435"

When running I get error "Run-time error 1004 - Unable to get the Find
Property of the Range Class"

Any ideas?
 
If you using Excel 97, then change the takefocusonClick property of the
commandbutton to false.
 
Thanks Tom - As always that solved the problem

Trouble is, when the error was fixed it should have found that value as it
does exist in the 'Detail' worksheet but I found that Range rngCell was set
to Nothing following execution of line of code with ethe Find statament.
 
assume that value is in cell B9 in detail and obviously A1 in LU.

In B1 of LU put in the formula (or in any blank cell in LU)

=A1=Detail!B9

does it return true. If not, figure out why not.
 
Back
Top