Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Finding ADDRESS of cells in range

  1. #1
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,
    In my macro's I often use:

    set myRange = ActiveCell.CurrentRegion

    which allows refering to changing ranges.
    Now, I need to know the ADDRESS of some cells in that range and put into variable: say the first cell in the last column, or the third from top, second to the right. I need adress not content (e.g. "H1").
    What is the VBA code for that?
    Many thanks,
    Eli

    [ This Message was edited by: eliW on 2002-04-26 02:18 ]

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi. Here is sample.


    Sub test()
    Dim myRange As Range
    Set myRange = ActiveCell.CurrentRegion
    With myRange
    'first cell in the last column
    MsgBox .Item(1).Offset(, .Columns.Count - 1).Address(0, 0)
    'the third from top
    MsgBox .Item(1).Offset(2).Address(0, 0)
    'second to the right
    MsgBox .Item(1).Offset(, .Columns.Count - 3).Address(0, 0)
    End With
    End Sub




    [ This Message was edited by: Colo on 2002-04-26 02:32 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-26 02:16, eliW wrote:
    Hi all,
    In my macro's I often use:

    set myRange = ActiveCell.CurrentRegion

    which allows refering to changing ranges.
    Now, I need to know the ADDRESS of some cells in that range and put into variable: say the first cell in the last column, or the third from top, second to the right. I need adress not content (e.g. "H1").
    What is the VBA code for that?
    Many thanks,
    Eli

    [ This Message was edited by: eliW on 2002-04-26 02:18 ]
    Hi Eli
    Have a look @ this code and take not of the
    comments;
    To test it make sure you have a valid range
    area and select within this range hen run it.




    Sub Range_Address()
    Dim MyRange As Range
    Dim oRow As Double, oCol As Double
    Dim x, y

    Set MyRange = Selection.CurrentRegion

    '// The oRow/oCol settings are important
    '// because the item method used on a range
    '// of cells will return results Even if they
    '// are outside of the range and you would
    '// only want a valid item address within the range you specify.
    oRow = MyRange.Rows.Count
    oCol = MyRange.Columns.Count

    MsgBox "Range address:=" & MyRange.Address

    For x = 1 To oRow
    For y = 1 To oCol
    MsgBox "Valid Addresses in " & MyRange.Address & ":=" & _
    MyRange.Item(x, y).Address
    Next
    Next

    MsgBox "InValid Addresses in " & MyRange.Address & ":=" & _
    MyRange.Item(10, 10).Address

    End Sub





    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Colo and Ivan
    Thank you for your answers
    Eli

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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