VBA Find produces 1004 error
 

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?

VBA Find produces 1004 error
 

If you using Excel 97, then change the takefocusonClick property of the
commandbutton to false.

--
Regards,
Tom Ogilvy



"Andy" wrote:

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?

VBA Find produces 1004 error
 

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.

"Tom Ogilvy" wrote:

If you using Excel 97, then change the takefocusonClick property of the
commandbutton to false.

--
Regards,
Tom Ogilvy



"Andy" wrote:

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?

VBA Find produces 1004 error
 

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.

--
Regards,
Tom Ogilvy


"Andy" wrote:

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.

"Tom Ogilvy" wrote:

If you using Excel 97, then change the takefocusonClick property of the
commandbutton to false.

--
Regards,
Tom Ogilvy



"Andy" wrote:

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?