R1C1 with Variables

[kaplin]

When I do this:

'text = "=subtotal(9," & Chr(64 + col) & "2:" & Chr(64 + col) & lastrow & &quot"

where col and lastrow are integers, I get an error because it inserts single parenthesis into the formala in the cell.

Ex: =subtotal(9,'A20':'B60')

Any idea how I get rid of the parenthesis - am I concatinating wrong?

 

[Zathras]

I can't tell what you are realy trying to do, but this may point you in the right direction:

Sub test()
  col = 2
  lastrow = 40
  Cells(1, 1).Formula = "=subtotal(9," & Chr(64 + col) & "2:" & Chr(64 + col) & lastrow & ")"
End Sub

 

[kaplin]

Thanks! I didn't realize there was a difference between Formula and FormulaR1C1. Changing it did the trick though..