Relative vs Absolute References (VBA Code)

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Hi,

If I'm recording a macro and specify to use relative
references, why does the VBA code look as though it is
specifying absolute cell references?

I.e.,

ActiveCell.FormulaR1C1 = "Test"
ActiveCell.Offset(1,0).Range("A1").Select

Why is A1 listed here...considering I'm running this in
another part of the worksheet say I42?

Thanks

Jerry
 
What you're seeing is just a by-product of how Excel records macros.
Actually, the Range("A1") part is not needed. So if it bothers you, you can
edit your code like this:

ActiveCell.FormulaR1C1 = "Test"
ActiveCell.Offset(1, 0).Select

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss
 
That may look like an absolute address, but it's not.

range("a1").select
would mean to select A1 in current worksheet.

But
ActiveCell.Offset(1,0).Range("A1").Select
means to come down 1 row (& stay in the same column), then use that cell.

..range("a1") refers to that topleft cell of the this range
"activecell.offset(1,0)"

It's easier to see it than describe it (well, for me).

put this sub in a test workbook:

sub testit()
msgbox ActiveCell.Offset(1,0).Range("A1").address
end sub

now go back to the worksheet and select a cell (say C9), then run the macro.

Try changing the A1 to C3 and see what happens.

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print that have some more variations for using ranges.
http://www.cpearson.com/excel/cells.htm
 
Back
Top