Return Row Index and Column Index of a Cell in a Range

Originally Posted by exceere

nilem,

Thank you for your try

But is there an instruction (or function) that return Row index like (.RowIndex) instead of looping through all cells in the range

Hi,
. I am not sure exactly wot you finally want to do.
. But as regards getting a set of indexes for a given Range "without looping", this code would return the row and column Index of a given range. The main part of the code is just one line.
. In this case the Row Index and Column index are written in the cell in question. But the code could probably be modified to write those Indexes to an Array.

Sub EvaluateRowColumn()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C4:E6")

Let rng.Value = evaluate("Row(" & rng.Address & ")" & "&"" , ""&" & "Column(" & rng.Address & ")")

End Sub 'EvaluateRowColumn()

Note: You may need to be careful about wot Cell Format you have to avoid Excel changing the out put to a date. best is to set the default to textAnother way to get over the problem would not initially tu use a commer. use anything else like so (using an & )

Sub EvaluateRowColumn()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("C4:E6")

Let rng.Value = evaluate("Row(" & rng.Address & ")" & "&"" & ""&" & "Column(" & rng.Address & ")")

End Sub 'EvaluateRowColumn()

. Any subsequent code change to write the values to an array would need an extra bit roughly of the form
Replace( _______ ,"&",",")

Alan