I want to be able to execute a macro (stored in PERSONAL.XLS) that puts the following statement in the cell two rows below the bottom row of a worksheet (regardless of how many rows)
=SUBTOTAL(3,A2,Axxxx
where xxxx is the last populated row. The following gets most of the way there
Set dataSheet = ActiveShee
totalrows = dataSheet.UsedRange.Rows.Coun
startrow = totalrows +
Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,A2:R[-2]C[0])
The above puts the following in the correct cell, but I can't figure out how to get A2 and not 'A2
=SUBTOTAL(3,'A2':A3839) && where 3839 happens to be the last row and is what I want

Can anyone help

Try this:

Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,R[-" & CStr(startrow - 1) & "]C[0]:R[-2]C[0])"

I had to do a -1 because apparently if you go up to far, it will start over at the bottom (I kept getting 65536 with just startrow).
"darrelstickler" wrote in message ...
I want to be able to execute a macro (stored in PERSONAL.XLS) that puts the following statement in the cell two rows below the bottom row of a worksheet (regardless of how many rows).
=SUBTOTAL(3,A2,Axxxx)
where xxxx is the last populated row. The following gets most of the way the
Set dataSheet = ActiveSheet
totalrows = dataSheet.UsedRange.Rows.Count
startrow = totalrows + 2
Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,A2:R[-2]C[0])"
The above puts the following in the correct cell, but I can't figure out how to get A2 and not 'A2'
=SUBTOTAL(3,'A2':A3839) && where 3839 happens to be the last row and is what I want.

Can anyone help?

Sorry, just noticed you wanted A2 and not A1, so either put (startrow - 2) or (totalrows)

Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,R[-" & CStr(totalrows) & "]C[0]:R[-2]C[0])"

"Medemper" wrote in message ...
Try this:

Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,R[-" & CStr(startrow - 1) & "]C[0]:R[-2]C[0])"

I had to do a -1 because apparently if you go up to far, it will start over at the bottom (I kept getting 65536 with just startrow).
"darrelstickler" wrote in message ...
I want to be able to execute a macro (stored in PERSONAL.XLS) that puts the following statement in the cell two rows below the bottom row of a worksheet (regardless of how many rows).
=SUBTOTAL(3,A2,Axxxx)
where xxxx is the last populated row. The following gets most of the way the
Set dataSheet = ActiveSheet
totalrows = dataSheet.UsedRange.Rows.Count
startrow = totalrows + 2
Range("A" + CStr(startrow)).FormulaR1C1 = "=SUBTOTAL(3,A2:R[-2]C[0])"
The above puts the following in the correct cell, but I can't figure out how to get A2 and not 'A2'
=SUBTOTAL(3,'A2':A3839) && where 3839 happens to be the last row and is what I want.

Can anyone help?

That's amazing. Thanks. Don't know where you learned the syntax, but I'm just a beginning swimming in shark infested waters. :)