Find method problem

[Saaden]

Hey guys,

Im trying to use this find method and when I dont specify matching the whole it finds a value but only based on a partial match. If I specify to match the whole then it doesnt find my value. Any help would be great.

heres the example
when txtcount = "1" and search method is partial match it returns the cell with a value of 10
when txtcount = "1" and search method is whole match it returns nothing

a3:a30000 values are from 1 to 29998 at the moment for testing



code:

Private Sub CommandButton1_Click()
Dim lrange As Range
Set lrange = Worksheets("counts").Range("a2:a30000")
If Me.txtCount.Value = "" Then
MsgBox ("Please enter a count number")
GoTo over
End If
Set c = lrange.Find(CLng(txtCount), after:=a2, LookIn:=xlValues, lookat:=xlwhole)

If c Is Nothing Then
MsgBox ("Count not found, please re-enter")
Me.txtCount.Value = ""
Me.txtCount.SetFocus
GoTo over
Else
MsgBox ("Count found " & c)
End If


over:
End Sub



Thanks for any help

[Philcjr]

Saaden,

Welcome to the board :hi:

Try this code:


Private Sub CommandButton1_Click()
Dim lrange As Range
Set lrange = Worksheets("counts").Range("a2:a30000")

If Me.txtCount.Value = "" Then
MsgBox ("Please enter a count number")
GoTo over
End If

Set c = lrange.Find(CLng(txtCount), , , xlWhole)
c.Select

If c Is Nothing Then
MsgBox ("Count not found, please re-enter")
Me.txtCount.Value = ""
Me.txtCount.SetFocus
GoTo over
Else
MsgBox ("Count found " & c)
End If

over:

End Sub


Let me know if this works for ya

Phil

[mdmackillop]

Hi Saaden
Assuming column A is not set as Text, the following seach line should suffice.
Set c = lrange.Find(txtcount)
BTW, c should also be dimmed as Range