MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return RELATIVE Cell Reference

Posted by JAF on June 20, 2001 8:45 AM


I've managed to return using VBA a cell address using the following code:

Sub Cell_Reference()
cell_ref = Range("D5").Address
MsgBox cell_ref
End Sub

The problem is that the reference returned is absolute - i.e. the message box returns $D$5 and II need the reference to be relative - i.e. D5

I'm using the cell reference as part of a formula in a macro and that formula is later copied down for a specified number of rows, so I need the reference to be relative.

I'm sure this is oen of those situations where there's another bit of code at the end of what I've already got, but I can't figure it out!!

Any help appreciated (as always)


Posted by JAF on June 20, 2001 8:48 AM

Duh! Call me "Mr Thicky"

Problem solved:

Sub Cell_Reference()
cell_ref = Range("D5").Address(False, False)
MsgBox cell_ref
End Sub