Which Element in Range?

[Stratuser]

I have code that finds a cell somewhere in a range. What is the code for
determining the item number of the cell within the range (i.e., the found
cell is the nth cell within the range).

[Gary''s Student]

Let's say you are finding the cell by its value then:


Sub Macro1()
Dim r As Range
Dim IAmTheCount, lookfor As Long
IAmTheCount = 0
lookfor = 123
For Each r In Selection
IAmTheCount = IAmTheCount + 1
If r.Value = lookfor Then
Exit For
End If
Next
MsgBox (IAmTheCount)
End Sub

Will look thru a range (in this code Selection) for the first cell
containing 123 and then output the "item" number in the range.

You will find that it goes across rows and then down columns until it finds
what it wants.
--
Gary''s Student


"Stratuser" wrote:

> I have code that finds a cell somewhere in a range. What is the code for
> determining the item number of the cell within the range (i.e., the found
> cell is the nth cell within the range).
>
>
>
>

[Tom Ogilvy]

Sub AAA()
Set rng = Range("B6:Z26")
Set cell = rng.Find("ABCD")
If Not cell Is Nothing Then
rw = cell.Row - rng(1).Row + 1
col = cell.Column - rng(1).Column + 1
MsgBox "row: " & rw & " column: " & col
End If

End Sub

--
Regards,
Tom Ogilvy



"Stratuser" <Stratuser@discussions.microsoft.com> wrote in message
news:A713B30D-5604-4C1A-BA63-D94B071F68C1@microsoft.com...
> I have code that finds a cell somewhere in a range. What is the code for
> determining the item number of the cell within the range (i.e., the found
> cell is the nth cell within the range).
>
>
>
>

[Charlie]

I presume you are searching a one-dimension range, i.e. all or part of one
row or one column, it would be something like this:

(I put "Test" into cell "D1" and am searching the entire row 1 starting at
cell "C1", ItemNo returns 2)

Dim ItemNo As Long

ItemNo = Range("C1").EntireRow.Find( _
What:="Test", LookIn:=xlValues, _
LookAt:=xlWhole).Column - _
Range("C1").Column + 1


MatchingCell

"Stratuser" wrote:

> I have code that finds a cell somewhere in a range. What is the code for
> determining the item number of the cell within the range (i.e., the found
> cell is the nth cell within the range).
>
>
>
>