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 -

    Code
    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.
    Dave

  • Re: Using Variants in Macro Formulas


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

    Code
    ActiveCell.Offset(-1, 0).Select
     strBottom = ActiveCell.Address
        
     Selection.End(xlUp).Select
     ActiveCell.Offset(1, 0).Select
     strTop = ActiveCell.Address
       
     Selection.End(xlDown).Select
     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!

    Code
    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,
    Dave

Participate now!

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