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

Hi,

I want code like following:

Please Login or Register  to view this content.

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

RowColIndex.PNG

maybe so

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

instead of the following code:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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 & )

Please Login or Register  to view this content.

. 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.

Please Login or Register  to view this content.

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 & )

Please Login or Register  to view this content.

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

Alan

What's wrong with

Please Login or Register  to view this content.

?

Originally Posted by jindon

What's wrong with

Please Login or Register  to view this content.

?

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:

Please Login or Register  to view this content.

)

. 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…

Please Login or Register  to view this content.

… but this does not?!?

Please Login or Register  to view this content.

.. 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

Please Login or Register  to view this content.

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

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.