Non-absolute verson of Range.Address

Shads

New Member
Joined
Dec 3, 2014
Messages
5
Is there a non-absolute version of Range.Address? I have a VBA code that builds the outline of a spread-sheet with formula's, formatting etc. It contains a loop which puts formula into a column but I would like the formula to use relative addresses so that later on I could extend the number of rows covered from within Excel if needed (using the cells' bottom-right handle thingy). Range(...).Address returns the address like $E$8 whereas I would like E8. Do I have to write a string-slicing function or is there a property of Range that can be called. Example:

Code:
[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3]Dim C_max_row As Long, i As Long

C_Max_row = 25

'...

For i = 2 To C_Max_row

    With ActiveSheet.Cells(i, 5)

        .Formula = "=IFERROR(LOOKUP(" & .Offset(0, -3).Address & "$F$2:$L$2,$F$1:$L$1),"""")"

        '...

    End With

Next i

[/SIZE][/FONT][/SIZE][/FONT]
Thanks
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,692
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Use:
Code:
.Offset(0, -3).Address(0,0)
 

spicetrader

New Member
Joined
Jun 23, 2011
Messages
9
Rory...
I've been working around this problem and cursing Microsoft about it for years.
Finally, you have saved me!
Excellent! Marvelous!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,244
Messages
5,413,281
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top