Finding exact matches only

[thomas.szwed]

Hello again!


I have a query regarding some code to help me find an exact value in sheet. I need to know how to find an exact number from a range of cells as opposed to finding one number character that could be say 8 digits long. For example my current code is -



Private Sub btnSelectRecord_Click()

Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim i As Integer
i = 1

Set rSearch = Sheet1.Range("a2", Range("d65536").End(xlUp))
strFind = Me.txtPersNum.Value 'text box on userform holding the number we are looking for
With rSearch
Set c = Cells.Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select

ActiveCell.Offset(0, -3).Activate ' used for other process i have spinning of this

i = i + 1
Set c = Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

End Sub



Say for instance the number i am looking for is 8.....it will search the range to find any number with 8 inside it e.g. so it could be 2008. Which is wrong, because i only want it to find the number i am looking for in the case is just 8 on its own.....Is this Cells.Find code or something because i cant get it to work.......appreciate your help

Tom

[rory]

You need to specify that in the find:
[VBA]Set c = Cells.Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)[/VBA]

[thomas.szwed]

Rory, thankyou - dont i already have this built in? Still doesnt work......Could you perhaps enlighten me to some code that performs a find on only exact matches.........?

[RichardSchollar]

Thomas

Is this happening when you're try Rory's suggestion ie so your code looks like:

[vba]Private Sub btnSelectRecord_Click()

Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim i As Integer
i = 1

Set rSearch = Sheet1.Range("a2", Range("d65536").End(xlUp))
strFind = Me.txtPersNum.Value 'text box on userform holding the number we are looking for
With rSearch
Set c = Cells.Find(strFind, LookIn:=xlValues,LookAt:=xlWhole)
If Not c Is Nothing Then 'found it
c.Select

ActiveCell.Offset(0, -3).Activate ' used for other process i have spinning of this

i = i + 1
Set c = Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

End Sub[/vba]

Richard

[Bob Phillips]

As Rory said

[vba]

Range("A1").Select
Cells.Find(4, , , xlWhole).Select
[/vba]

[Bob Phillips]

BTW, find remembers the last values, so it is best to alkways be specific.

[thomas.szwed]

XLD - Thanks - but how can i fit this in correctly with my new code - see below-


Private Sub btnSelectRecord_Click()
With Range("D265000")

Set c = .Cells.Find(what:=Me.txtPersNum.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
c.Offset(0, -3).Activate
With Me

.btnEdit.Enabled = True 'allow amendment or
.btnDelete.Enabled = True 'allow record deletion
.btnAddRecord.Enabled = False 'don't want duplicate
.btnExport.Enabled = True

End With

End Sub

[rory]

I just did! You need the LookAt:=xlWhole to specify that it should match the whole cell not just part of it.

[thomas.szwed]

I see, you use the xlwhole - definement! now i know thanks!