Using Variants in Macro Formulas

  • Hi,
    I am trying to insert a simple subtotal formula in a macro, using variants as the bottom of the range will change.
    What I can't work out is the placement of the ""s and the &s

    I've already defined strTop as the top cell in the range and strBottom as the bottom cell (cell above the subtotal).

    Currently I have -

    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9," & strTop & ":" & strBottom & ")"

    I've tried this and a dozen+ variations but Excel won't accept them!!
    Any help - and general advice on how to use the ""s and &s... in these situations - greatly appreciated.

    Also, is there a simpler way to do this? I need to copy this formula across the next 53 columns, and the only way I can think of is to use the above and then redefine StrTop and StrBottom each time for the next 53 columns. :?

    Many, many thanks for any help.

  • Re: Using Variants in Macro Formulas

    Hi Norie,
    My problem was Run-time Error 1004
    The full code I was trying to use was:

    ActiveCell.Offset(-1, 0).Select
     strBottom = ActiveCell.Address
     ActiveCell.Offset(1, 0).Select
     strTop = ActiveCell.Address
     ActiveCell.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "=SUBTOTAL(9," & strTop & ":" & strBottom & ")"

    I have just found a way round it though - much simpler & makes the next 53 columns much easier!

    y = ActiveCell.Row
     x = y - 11
     ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & x & "]C:R[-1]C)"

    I'd still be interested what's wrong with the first one though as I keep on coming across this problem ...

    Many thanks,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!