Macro creates formula with $ absolute. I want to drop the $

A

Aaron

I've got this

LastRow = Cells(Rows.Count, "H").End(xlUp).Row
Set rng = Range("H2:H" & LastRow)
ActiveCell.Formula = "=SUBTOTAL(9," & rng.Address & ")"

Which gives me this on the spreadsheet:
=SUBTOTAL(9,$H$2:$H$1122)

But I want this:
=SUBTOTAL(9,H2:H1122)

How do I stop the $?
 
I

ilia

In Excel 2003 or earlier, as soon as you start recording, the macro
recorder toolbar floats out on the screen. Next to the Stop button,
there's a toggle button for relative references - when toggled on,
macro recorder will use relative references.

In Excel 2007, this option appears in the Code group of the Developer
tab.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top