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


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</pre>

 

[eliW]

Colo and Ivan
Thank you for your answers
Eli