Find problem

[Francis Hookham]

Column 1 contains the numeric ID of about 2,000 rows.

A variable iID, or should it be sID, records the ID before sorting by
another column.

I then need to find which particular row contains the ID, select it and
scroll to it.



The problem:

Let us say the ID is 27

After sorting 27 is below 275 so 'Find' finds 275 first!



How can I find the row which contains the ID 27 and not a larger number
containing the original digits?



Francis Hookham

 

[Gary Keramidas]

i had to hard code some of it, but give this a try. there is not enough
information about your data.

Sub test()
Dim ws As Worksheet
Dim rngfound As Range
Dim rng As Range
Dim sID As String ' not sure what you're looking for
Set ws = Worksheets("Sheet1") ' change this
Set rng = ws.Range("A1:A30") ' change this
sID = 27 ' hard coded
With rng
Set rngfound = .Find(sID, lookat:=xlWhole, LookIn:=xlValues)
End With
If Not rngfound Is Nothing Then
Application.Goto ws.Range("A" & rngfound.Row), scroll:=True
End If

End Sub

 

[Gary''s Student]

Even without VBA:

Edit > Find
and make sure entire cell contents is checked.

With VBA

 

[Rick Rothstein \(MVP - VB\)]

If I understand your question correctly, click the Options>> button on the
Find dialog and select the "Match entire cell contents" option (along with
the Search "By Columns" option I would guess).

Rick

 

[Francis Hookham]

Thanks Gary, Gary's Student and Rick

It is the
LookIn:=xlValues, LookAt :=xlWhole,
that does the trick.

I should have looked in the Options - I always should but don't always
remember!

As always you guys do a fantastic job.

Francis


Sub FindSameNames()
Application.ScreenUpdating = False
iRefNum = Cells(ActiveCell.Row, 1)
SortByID 'macro to sort col 1
SortBySubject 'macro to sort col 5
Columns("A:A").Select
Selection.Find(What:=iRefNum, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
iRowNum = ActiveCell.Row
ActiveWindow.ScrollRow = iRowNum
Cells(iRowNum, 5).Select
End Sub

 

[Francis Hookham]

Apologies - I thought I had sent this but cannot find it:

Thanks Gary, Gary's Student and Rick

It is the
LookIn:=xlValues, LookAt :=xlWhole,
that does the trick.

I should have looked in the Options - I always should but don't always
remember!

As always you guys do a fantastic job.

Francis


Sub FindSameNames()
Application.ScreenUpdating = False
iRefNum = Cells(ActiveCell.Row, 1)
SortByID 'macro to sort col 1
SortBySubject 'macro to sort col 5
Columns("A:A").Select
Selection.Find(What:=iRefNum, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
iRowNum = ActiveCell.Row
ActiveWindow.ScrollRow = iRowNum
Cells(iRowNum, 5).Select
End Sub