PDA

View Full Version : Finding exact matches only



thomas.szwed
12-05-2007, 10:04 AM
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
12-05-2007, 10:44 AM
You need to specify that in the find:
Set c = Cells.Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)

thomas.szwed
12-06-2007, 04:00 AM
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
12-06-2007, 04:35 AM
Thomas

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

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

Richard

Bob Phillips
12-06-2007, 04:38 AM
As Rory said



Range("A1").Select
Cells.Find(4, , , xlWhole).Select

Bob Phillips
12-06-2007, 04:40 AM
BTW, find remembers the last values, so it is best to alkways be specific.

thomas.szwed
12-06-2007, 04:47 AM
XLD - Thanks - but how can i fit this in correctly with my new code - see below-


Private Sub btnSelectRecord_Click()
With Range("D2:D65000")

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
12-06-2007, 04:49 AM
I just did! :) You need the LookAt:=xlWhole to specify that it should match the whole cell not just part of it.

thomas.szwed
12-06-2007, 04:49 AM
I see, you use the xlwhole - definement! now i know thanks!