Finding ADDRESS of cells in range

[eliW]

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 ]

[Colo]

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 ]

[Ivan F Moala]

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

[eliW]

Colo and Ivan
Thank you for your answers
Eli