Relative Cell Refrences in Macros

[DSerr77]

I am having a problem incorporating relative cell refrences into a macro I am writing (for example: Range.("RC[+1]").Select). Can anyone tell me what I am doing wrong?
Thanks!

 

[SkipVought]

Hi,

I don't mess with relative cell references in macros. You can use the Offset Method and get correct results.

For instance next row same column...

Activecell.Offset(1, 0)

If you need the address

Activecell.Offset(1, 0).Address

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[macropod]

For cell selection/activation etc, I'd certainly recommend Skip's approach. Likewise, if you want to insert a formula that always points to the same absolute address, use the Formula= approach. If you want to insert a formula using the same relative addressing scheme, however, I'd recommend using the FormulaR1C1= approach because it allows to us use just the one formula instead of a different one for each target cell.

Cheers

 

[xlbo]

Actually, excel / vba has enough smarts so that if you leave the $ out of formulae using the range.formula rather than range.formulaR1C1, it will be able to calc as if it was relative eg

Range("B1:B100").formula = "=vlookup(A1,datarng,2,false)"

will copy the formula to range B1:B100 - B1 will lookup A1 and B100 will lookup A100 - nice and relative !!

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question