Find problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Hookham - 28 May 2008 19:41 GMT

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

Reply to this Message

Gary Keramidas - 28 May 2008 19:55 GMT

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

Signature

Gary

> Column 1 contains the numeric ID of about 2,000 rows.
>
[quoted text clipped - 14 lines]
>
> Francis Hookham

Reply to this Message

Gary''s Student - 28 May 2008 19:58 GMT

Even without VBA:

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

With VBA

Signature

Gary''s Student - gsnu200789

> Column 1 contains the numeric ID of about 2,000 rows.
>
[quoted text clipped - 14 lines]
>
> Francis Hookham

Reply to this Message

Rick Rothstein (MVP - VB) - 28 May 2008 19:59 GMT

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

> Column 1 contains the numeric ID of about 2,000 rows.
>
[quoted text clipped - 14 lines]
>
> Francis Hookham

Reply to this Message

Francis Hookham - 29 May 2008 17:16 GMT

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

Reply to this Message

Francis Hookham - 31 May 2008 18:09 GMT

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

Reply to this Message