Cells Index

[mdmackillop]

For non-experts!

To assist in a Find routine, I want to record the Index of the cells as they are found.

This will give me the address of Cell 1000,
[vba]
MsgBox Cells(1000).Address
[/vba]
but given an address, what is the simplest way to get the cell index of a specified cell e.g. AX24

And for completion, the index of Range("Test").Range("A4"), relative to the range.

The solution must be 2003 and 2007 compatible.

[p45cal]

[vba]Sub blah()
Dim xxx As Range
Dim mySheetIndex, myRangeIndex
Set xxx = Cells.Find("Hello", , xlValues)
mySheetIndex = (xxx.Row - 1) * Columns.Count + xxx.Column
Debug.Print "Index on sheet is " & mySheetIndex
Cells(mySheetIndex).Select
'given the A4 part:
myRangeIndex = (Range("A4").Row - 1) * Range("Test").Columns.Count + Range("A4").Column
Range("Test").Cells(myRangeIndex).Select
End Sub
[/vba](not thoroughly tested/explored)

[p45cal]

Have I made a gaffe by trying to answer this one?!

[mdmackillop]

Well I thought I would pose it for "beginners" but all thoughts welcome! Part 2 though is not quite right.
In Range("Test").Range("A4"), A4 is relative to a named range, and the cells index should reflect that. ie Range("Test").Cells(50) depends upon the location and size of the range.

In practice, I'll be looking to make up a function in the form

Function MyIndex(Addr as String, Optional Rng as Range)

[GTO]

Hi Malcom,

Thank you for this, very educational! I sheepishly admit that I didn't even realize you could refer to Cells with just the one number! (Yes - I see it's right there in the Help topic )

I do not believe I've ever tried using the range relative to a given range either. Thus, this got a bit lengthier than intended (learning), but please see if I was on track or missed anything.

[vba]
Option Explicit

Sub RecordFinds_3()

Dim _
i As Long, _
lULimit As Long, _
lRow As Long, _
lCol As Long, _
rngTest As Range, _
rngFoundCell As Range, _
strFAddress As String, _
strMsg As String, _
lRelativeIndex As Long, _
aryIndex() As Variant, _
aryIndexOffset() As Long

Const LOW_ROW As Long = 3
Const LOW_COL As Long = 3
Const HI_ROW As Long = LOW_ROW + 49
Const HI_COL As Long = LOW_COL + 49

Clear:
'// Subjective, just clearing well past any test range. I wanted to be able to //
'// move the Test range around a bit, as was having a bit of a time getting my head //
'// around relative return of rngTest.Range("A4"). //
Range("A2:CV100").Clear
Setup:
Set rngTest = Range(Cells(LOW_ROW, LOW_COL), Cells(HI_ROW, HI_COL))

Randomize
'// Randomly return(create) 10 to 20 "Find Me!" vals//
lULimit = Int((20 - 10 + 1) * Rnd + 10)

'// Randomly place "Find Me!" vals in the Test range.//
For i = 1 To lULimit
lRow = Int((rngTest.Row + rngTest.Rows.Count) * Rnd + rngTest.Row)
lCol = Int((rngTest.Row + rngTest.Rows.Count) * Rnd + rngTest.Row)
Cells(lRow, lCol).Value = "Find Me!"
Next

'// More oft than I would have suspected, one or more of the randomly placed vals //
'// will end up in the same cell; so reset lULimit to how many "Find Me!"s were //
'// actually placed in our test range. //
lULimit = Application.WorksheetFunction.CountIf(rngTest, "Find Me!")

MsgBox "There should be " & lULimit & " cells found.", 64, vbNullString

LookOutLookOutReadyOrNotHereICome:
With rngTest
Set rngFoundCell = .Find(What:="Find Me!", _
After:=rngTest(50, 50), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

If Not rngFoundCell Is Nothing Then
ReDim aryIndex(1 To 2, 1 To lULimit)
i = 1
'// Record ea found cell's Index//
aryIndex(1, i) = ((rngFoundCell.Row - 1) * Columns.Count) _
+ rngFoundCell.Column
'// Record ea found cell's address//
aryIndex(2, i) = rngFoundCell.Address(False, False)
'// Record first found cell's address to kill looping later//
strFAddress = rngFoundCell.Address

Do
Set rngFoundCell = .FindNext(After:=rngFoundCell)
If Not rngFoundCell.Address = strFAddress Then
i = i + 1
'// Continue recording found vals' Indexes and Addresses//
aryIndex(1, i) = ((rngFoundCell.Row - 1) * Columns.Count) _
+ rngFoundCell.Column
aryIndex(2, i) = rngFoundCell.Address(False, False)
End If
Loop While Not rngFoundCell Is Nothing _
And Not rngFoundCell.Address = strFAddress

ReDim aryIndexOffset(1 To lULimit)

lRelativeIndex = (((rngTest.Range("A4").Row - 1) * Columns.Count) _
+ rngTest.Range("A4").Column)

For i = 1 To lULimit
aryIndexOffset(i) = aryIndex(1, i) - lRelativeIndex
Next

strMsg = "rngTest Address is:" & String(4, vbTab) & _
rngTest.Address(False, False) & vbCrLf & _
"Relative Address to rngTest.Range(""A4"") is:" & vbTab & _
rngTest.Range("A4").Address & vbCrLf & _
"Index to rngTest.Range(""A4"") is:" & String(2, vbTab) & _
lRelativeIndex & String(1, vbCrLf) & _
String(50, Chr(175)) & vbCrLf & _
String(2, vbTab) & "CELLS FOUND: " & lULimit & vbCrLf & _
String(50, Chr(175)) & vbCrLf & _
"Address:" & vbTab & "Index:" & vbTab & "Index Offset:" & vbCrLf

For i = 1 To lULimit
strMsg = strMsg & aryIndex(2, i) & vbTab & aryIndex(1, i) & vbTab & _
aryIndexOffset(i) & vbCrLf
Next

MsgBox strMsg
End If
End With
End Sub
[/vba]

Thank you so much, and thanks again for the great "test question",

Mark

[p45cal]

re:

Part 2 though is not quite right.
In Range("Test").Range("A4"), A4 is relative to a named range, and the cells index should reflect that. ie Range("Test").Cells(50) depends upon the location and size of the range.

In practice, I'll be looking to make up a function in the form

Function MyIndex(Addr as String, Optional Rng as Range)

Did you mean that my solution to part 2 was not quite right? I think it works OK..
The function based on the same algorithm:[vba]Function MyIndex(Addr As String, Optional Rng As Range)
If Rng Is Nothing Then Set Rng = Cells
MyIndex = (Range(Addr).Row - 1) * Rng.Columns.Count + Range(Addr).Column
End Function
[/vba]Use like this:[vba]zz = MyIndex("A4", Range("Test"))[/vba]Even useable on a worksheet:
=MyIndex("A4",Test)

see attached where I manually set up 2 named ranges and see code in module1.

[mdmackillop]

I'll check out your solution in detail, however I've made use of your function in this wee utility (2000/2003 only). Unfortunately, the programme can't handle Cells.Count in 2007