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,

MsgBox Cells(1000).Address

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]

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
(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 :banghead: )

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.


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


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: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
Use like this:zz = MyIndex("A4", Range("Test"))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