Hi,
I want code like following:
Which return the row index and column index of each cell in range C4:E6 as in following table:![]()
Please Login or Register to view this content.
RowColIndex.PNG
Hi,
I want code like following:
Which return the row index and column index of each cell in range C4:E6 as in following table:![]()
Please Login or Register to view this content.
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 (i,j)
Last edited by exceere; 01-03-2015 at 06:03 AM.
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
Where do you want to return .RowIndex. A Cell, A Messagebox.???But is there an instruction (or function) that return Row index like (.RowIndex) instead of looping through all cells in the range
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Maybe this
![]()
Please Login or Register to view this content.
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:
instead of the following code:![]()
Please Login or Register to view this content.
![]()
Please Login or Register to view this content.
Im lost as to what your trying to accomplish but maybe this will work
![]()
Please Login or Register to view this content.
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.
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![]()
Please Login or Register to view this content.
Replace( _______ ,"&",",")
Alan
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.
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![]()
Please Login or Register to view this content.
Replace( _______ ,"&",",")
Alan
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…
… 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![]()
Please Login or Register to view this content.
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.
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
Here is the Link to the Thread I started yesterday, which was quickly solved:
http://www.excelforum.com/excel-new-...to-arrays.html
..
. Sorry I got that wrong. My code given here for 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks