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

Hi,

I want code like following:

Sub RowColIndex()

For Each Cll In Range("C4:E6")

    Cll.RowIndex
    Cll.ColumnIndex

Next

End Sub

Which return the row index and column index of each cell in range C4:E6 as in following table:

RowColIndex.PNG

maybe so

Sub RowColIndex()
Dim i As Long, j As Long
With Range("C4:E6")
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
            .Cells(i, j).Select
            MsgBox "RowIndex " & i & "; ColumnIndex " & j
        Next j
    Next i
End With
End Sub

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

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

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 (i,j)

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

mike7952, thank you for your Try

i'm searching for a function (.RowIndex / .ColumnIndex) that return the row index and column index for each cell in the range, so that when i want to move the range to an array i will use the following code:

Sub MovRngToArr()

ReDim Arr(1 To Range("C4:E6").Rows.Count, 1 To Range("C4:E6").Columns.Count)

For Each Cll In Range("C4:E6")

   Arr(Cll.RowIndex, Cll.colunmIndex) = Cll

Next Cll

End Sub

instead of the following code:

Sub MovRngToArr()

ReDim Arr(1 To Range("C4:E6").Rows.Count, 1 To Range("C4:E6").Columns.Count)

For i = 1 To Range("C4:E6").Rows.Count

    For j = 1 To Range("C4:E6").Columns.Count

       Arr(i, j) = Range("C4:E6").Cells(i, j)

    Next j

Next i

End Sub

What's wrong with

Arr = Range("C4:E6").Value

?

Originally Posted by jindon

What's wrong with

Arr = Range("C4:E6").Value

?

Hi jindon…
. If that is the final result that the OP wants then I would usually do exactly what you suggested. It is the common neat way to “capture” a Range into an Array (I think? – I am still learning these things!).

. I was not sure if the OP for some reason wanted an intermediate step with some record of the Indexes.(If that were the case this would be a code along both our lines giving an Array of those indexes:

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

Dim Arr() As Variant

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


End Sub 'ArrayEvaluateRowColumn()

)

. Possibly if the OP is new to VBA like me he does not quite understand how or why your simple code line works, but similar ones do not. Maybe that is the key to understanding how and why the code that works works! (Any insight there would be very welcome!)

For example, why does this work…

Sub jindonArr()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr() As Variant
Let Arr() = rng.Value
End Sub 'jindonArr()

… but this does not?!?

Sub jindonArr2()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr() As String
Let Arr() = rng.Value
End Sub 'jindonArr2()

.. I would have expected the second code to work as I had strings in the cells - as was confirmed by looking at the array values from the first code in the Watch Window







Alan
P.s. Another possibility would be that the OP wants an Array of Ranges. Again versions of your simple code just in case that is what he wants to do that would be

Sub jindonArrOfRanges()
Dim Arr As Range
Set Arr = Range("C4").CurrentRegion
End Sub 'jindonArrOfRanges()
'
'
'
'
'




Sub jindonArrOfRanges2()
Dim Arr As Range
Set Arr = ThisWorkbook.Worksheets("CrazyRanges").Range("C4").CurrentRegion
End Sub 'jindonArrOfRanges2()
Sub jindonArrOfRanges4()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr As Range
Set Arr = rng.Range("C4:E6")
End Sub 'jindonArrOfRanges4()


Sub jindonArrOfRanges3()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("CrazyRanges").Range("C4:E6")
Dim Arr As Range
Set Arr = rng.CurrentRegion
End Sub 'jindonArrOfRanges3()

Array from the range MUST be variant type.

Because it should hold various types of elements like numeric/string/boolean as Cell does.

Originally Posted by jindon

Array from the range MUST be variant type.

Because it should hold various types of elements like numeric/string/boolean as Cell does.

Hi jindon…

Ok. Many Thanks. If I think about it the Elements become a string by .Value, which is after the Range Object. So as that Object is initially assigned to the Array the Dimensioning must allow for that. There are a few similar anomalies in size Dimensioning etc. I do not quite grasp. But I will not hijack the Op’s Thread. I will have a good think again and then possibly start a thread in the Beginners “New Users/Basics” Forum
Thanks Again.
Alan

Hi Doc.AElstein, jindon, mike7952

Million Thanks for you

Iam very grateful for what you have done, special for mike7952 and Doc.AElstein

i think what have you done will be helpful for many people

Thank you very much

Originally Posted by exceere

Hi Doc.AElstein, jindon, mike7952

Million Thanks for you......

.. You are Welcome...First time I have managed to help anyone here in Excel Forum

Originally Posted by Doc.AElstein

Hi jindon…

Ok. Many Thanks. .......few similar anomalies in ....etc. I do not quite grasp. But I will not hijack the Op’s Thread. I ...... possibly start a thread in the Beginners ........

Here is the Link to the Thread I started yesterday, which was quickly solved:
http://www.excelforum.com/excel-new-...to-arrays.html




…………………………………..

Originally Posted by Doc.AElstein

…….. Another possibility would be that the OP wants an Array of Ranges. Again versions of your simple code just in case that is what he wants to do that would be........

. Sorry I got that wrong. My code given here for

Originally Posted by Doc.AElstein

..Array of Ranges.....

actually produces one Range Object for that range E3 – C6.

.
. Just to clear up any confusion, I did a code in that Thread: - ……..
http://www.excelforum.com/excel-new-...to-arrays.html
…………at the end I wrote and described a Macro using Spreadsheet Range C3 – E6 as an example which produces 3 things based on that Spreadsheet Range.
.1) An Array of the values in the cells in that Spreadsheet Range;
.2) One Range Object for That Range;
.3) An Array of all the Cells in that Spreadsheet Range as Range Objects

Hope that clears up any confusion caused by my then ignorance of the different ways of “Capturing” the stuff in a Spreadsheet Range

Alan.

Hi Doc.AElstein, jindon, mike7952

Million Thanks for you

Iam very grateful for what you have done, special for mike7952 and Doc.AElstein

i think what have you done will be helpful for many people

Thank you very much