Hi Experts out there,

Currently I'm having a problem with the the Find function
in VBA. I am actually looking for a cell in another
sheet, but I always get nothing in the range. Can
somebody please help on what is wrong with my code below?

Public Sub finding()

Dim rng As Range
Dim strRange As String
Dim FindText As String

Let strRange = "CB"
Worksheets("CurrentBalance").Range("CB").Select
Let FindText = "23-01-2004"
With Worksheets("CurrentBalance").Range(strRange)
Set rng = .Find(What:="23-01-2004")
End With

If Not rng Is Nothing Then
rng.Activate
Else
MsgBox "Not Found"
End If

End Sub

MANY THANKS IN ADVANCE....

LSB

Instead of having
Worksheets("CurrentBalance").Range("CB").Select

put

Worksheets("CurrentBalance").Activate

that made the code work for me.

Then just activate the other sheet afterwards.

Keith
www.kjtfs.com


---
Message posted from http://www.ExcelForum.com/

because you're looking for a Date instead of a text string or number, you need to use DATESERIAL to refer to it.
Look at the follow modified macro.

Public Sub finding()

Dim rng As Range
Dim strRange As String

strRange = "CB"
Worksheets("CurrentBalance").Range(strRange).Selec t
With Worksheets("CurrentBalance").Range(strRange)
Set rng = .Find(DateSerial(2004, 1, 23))
End With

If Not rng Is Nothing Then
rng.Select
Else
MsgBox "Not Found"
End If
End Sub

----- LSB wrote: -----

Hi Experts out there,

Currently I'm having a problem with the the Find function
in VBA. I am actually looking for a cell in another
sheet, but I always get nothing in the range. Can
somebody please help on what is wrong with my code below?

Public Sub finding()

Dim rng As Range
Dim strRange As String
Dim FindText As String

Let strRange = "CB"
Worksheets("CurrentBalance").Range("CB").Select
Let FindText = "23-01-2004"
With Worksheets("CurrentBalance").Range(strRange)
Set rng = .Find(What:="23-01-2004")
End With

If Not rng Is Nothing Then
rng.Activate
Else
MsgBox "Not Found"
End If

End Sub

MANY THANKS IN ADVANCE....

LSB