Using VBA to enter a "dynamic" formula into cells

  • Hi all,


    Here I am back, "thick as a brick" :hammerhe: with the same problem of entering a formula into a cell, where the row number should be pulled from a loop that contains the code.
    "r" is a row number from the loop that contains the code line, and each cell ref in the formula should inherent the respective "r" when it is entered into the cell.

    Code
    Range("I" & r).Formula = "=SUMPRODUCT(--(OrderFG=Budget!C3),--(OrderSgmt=Budget!A3]),--(OrderSR=Budget!F3),OrderQty)"


    So, if I am in r = 7 ,for example, the formula entered into cell I7 by the code should actually like this:

    Code
    =SUMPRODUCT(--(OrderFG=Budget!C7),--(OrderSgmt=Budget!A7]),--(OrderSR=Budget!F7),OrderQty)


    I have read all previous help provided to me here (on the very same topic), tried every double-quote combination I could think of (including using char(34) ) and came up with errors, each time...


    Again, I reach out to your support :confused:


    T.I.A.
    P.

  • Re: Using VBA to enter a "dynamic" formula into cells


    Untested, but see if this works

    Code
    Range("I" & r).Formula = "=SUMPRODUCT(--(OrderFG=Budget!C" & r & "),--(OrderSgmt=Budget!A" & r & "]),--(OrderSR=Budget!F" & r & "),OrderQty)"


    You basically have to remove the variable bit from inside quotes, so end the quotes before and add quotes afterwards. That said, if you're entering a formula across a range you shouldn't need to loop. E.g. this will change reference automatically

    Code
    Range("A1:A10").Formula = "=B1+5"
  • Re: Using VBA to enter a "dynamic" formula into cells


    Quote from StephenR;778079

    Untested, but see if this works

    Code
    Range("I" & r).Formula = "=SUMPRODUCT(--(OrderFG=Budget!C" & r & "),--(OrderSgmt=Budget!A" & r & "]),--(OrderSR=Budget!F" & r & "),OrderQty)"


    You basically have to remove the variable bit from inside quotes, so end the quotes before and add quotes afterwards. That said, if you're entering a formula across a range you shouldn't need to loop. E.g. this will change reference automatically

    Code
    Range("A1:A10").Formula = "=B1+5"


    Damn, you are good :rock:
    Maybe one day I will able to grasp this logic... (there was a tiny typing error, but I removed it. There was a "spare" ]... )


    BTW, the reason I have to do this line by line is derived from the fact that my loop includes a select-case. I am checking a certain condition in each individual line and enter a different code line according to the result.
    Never the less, I thank you for the second option and will use it elsewhere in the code.


    If you can spare the time, I would love to see how the same line will be incorporated in an "Evaluate" formula (meanwhile - I simply do a copy/paste special as values, in order to "flatten" the result).


    Thanks again, and god's bless !

  • Re: Using VBA to enter a "dynamic" formula into cells


    Sorry, probably my fat fingers.


    With Evaluate, you just have to wrap the whole thing in brackets. I think this should work but again haven't tested.

    Code
    Range("I" & r).value = evaluate("SUMPRODUCT(--(OrderFG=Budget!C" & r & "),--(OrderSgmt=Budget!A" & r & "),--(OrderSR=Budget!F" & r & "),OrderQty)")
  • Re: Using VBA to enter a "dynamic" formula into cells


    Quote from StephenR;778145

    Sorry, probably my fat fingers.


    With Evaluate, you just have to wrap the whole thing in brackets. I think this should work but again haven't tested.

    Code
    Range("I" & r).value = evaluate("SUMPRODUCT(--(OrderFG=Budget!C" & r & "),--(OrderSgmt=Budget!A" & r & "),--(OrderSR=Budget!F" & r & "),OrderQty)")


    Thanks Stephen !
    Will give it a second try (I tried to do it before writing but perhaps I mis-typed something)
    All the best
    P.

Participate now!

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