+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    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

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,350

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

    maybe so
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

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

    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.

  4. #4
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

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

    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

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    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.???
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    Maybe this

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

    Re: Return Row Index and Column Index of a Cell in a 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.

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

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

    Im lost as to what your trying to accomplish but maybe this will work

    Please Login or Register  to view this content.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by exceere View Post
    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

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by exceere View Post
    nilem,

    Thank you for your try

    But is there an instruction (or function) that return Row index like (.RowIndex) instead of looping (i,j)
    Quote Originally Posted by exceere View Post
    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

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,862

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

    What's wrong with
    Please Login or Register  to view this content.
    ?

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by jindon View Post
    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.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,862

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

    Array from the range MUST be variant type.

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

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by jindon View Post
    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

  15. #15
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

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

    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

  16. #16
    Registered User
    Join Date
    07-20-2012
    Location
    King
    MS-Off Ver
    Excel 2016
    Posts
    75

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

    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

  17. #17
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by exceere View Post
    Hi Doc.AElstein, jindon, mike7952

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

  18. #18
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by Doc.AElstein View Post
    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




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




    Quote Originally Posted by Doc.AElstein View Post
    …….. 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
    Quote Originally Posted by Doc.AElstein View Post
    ..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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 09-09-2014, 02:25 PM
  2. Replies: 4
    Last Post: 03-13-2013, 12:38 PM
  3. Replies: 17
    Last Post: 12-05-2012, 09:01 PM
  4. Replies: 2
    Last Post: 02-27-2010, 11:17 AM
  5. [SOLVED] Return contents of cell at Index position in Source Range of Drop
    By TheMath in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2006, 01:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts