Dynamic Formula VBA

  • Hello Ladies and Gents,

    What I am trying to do is make this Formula Dynamic. It is part of a much larger scenario but here is the gist. I can make it work for smaller Formulas but this one is throwing an Application Defined Error 1004

    I would like this formula inserted to a cell through VBA Dynamically.

    =IF(BA10<=0, 2, IF(BA10<>"", BA10, (MAX(AZ10,AY10))))

    This is what I am currently working at..

    Target.Formula = "=IF(BA" & Target.Row & "<=0" & "," & "" & "," & "If(BA" & Target.Row & "<>""" & "," & "BA" & Target.Row & "," & "(MAX(CA" & Target.Row & "," & "DA" & Target.Row & "))))"

    I have made it work previously but can not seem to get the syntax right. Any help would be great. Thanks so much

    P.S Formulas are not my strong point :)

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Dynamic Formula VBA

    Hi Yegarboy,
    maybe so

    '=IF(BA10<=0, 2, IF(BA10<>"", BA10, (MAX(CA10,DA10))))
    Dim i As Long
    i = Target.Row
    Target.Formula = "=IF(BA" & i & "<=0,2,If(BA" & i & "<>"""",BA" & i & ",(MAX(CA" & i & ",DA" & i & "))))"
  • Re: Dynamic Formula VBA

    Sorry if this isn't related,

    Can you help me fix this formula assignment?
    I get error 1004 on the ActiveCell.Formula line.

    Here is the relvent part:
    Nusha = "=IF(((RC[1])>RC12),""PASS"",IF(RC[1]=0,""NoRef"",IF((RC[1]>RC12),""AE"",""FAIL""))))"
    ActiveCell.Formula = Nusha

    Thanks alot.

Participate now!

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