Formula using Variables with VBA

Question

I am wrestling with the syntax of this one line of some code. the formula with "IfError" seems to have the wrong syntax. I believe I have the quotes in the right places.

    Dim j As Integer
    Dim MidPointE As String
    Dim Dist As String
    Dim Allocation As String

    MidPointE = "AM"
    Dist = "AN"
    Allocation = "AO"

   for J= 1 to 300
     Cells(j, Dist).Formula = "=IFERROR(" & MidPointE & j & " / " & MidPointE &  CustomerLast & ", "")"
    Cells(j, Allocation).Formula = "=" & Allocation & j & "* S" & CustomerLast

     Next J

FWIW - Cells(j, Allocation).Formula = "=" & Allocation & j & "* S" & CustomerLast (after you assign a value to CustomerLast) is going to cause circular reference errors. — YowE3K
– YowE3K, Commented Nov 15, 2017 at 23:25
Thank you! I had tried that before but getting confirmation suddenly made it work! — Purpletiger
– Purpletiger, Commented Nov 16, 2017 at 15:30

Slai · Accepted Answer · 2017-11-15 23:48:28Z

1

You can also assign multiple formulas at the same time:

Range("AN1:AN300").Formula = "=IFERROR(AM1 / AM$" &  CustomerLast & ", """")"
Range("AO1:AO300").Formula = "=AO1 * S$" & CustomerLast

I also recommend looking into Excel Tables and Structured References

To avoid the circular reference issue, you can calculate the formulas and assign the values directly:

Range("AN1:AN300") = Evaluate("IFERROR(AM1:AM300 / AM$" &  CustomerLast & ", """")")
Range("AO1:AO300") = Evaluate("INDEX(AO1:AO300 * S$" & CustomerLast & ",)")

edited Nov 15, 2017 at 23:48

answered Nov 15, 2017 at 23:37

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YowE3K Over a year ago

And that AO cell is still going to cause a circular reference - but only the OP can solve that.

Mathieu Guindon · Accepted Answer · 2017-11-15 23:26:02Z

1

Try this:

 Cells(j, Dist).Formula = "=IFERROR(" & MidPointE & j & " / " & MidPointE &  CustomerLast & ", """")"

Quotes inside a string literal must be escaped - and that's done by doubling them up.

edited Nov 15, 2017 at 23:26

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Nov 15, 2017 at 23:25

Eliot

915 bronze badges

Comments

SuperStew · Accepted Answer · 2017-11-15 23:26:00Z

0

It's at the end here

& ", "")"

The two quotes between the comma and ) end the string and start a new one.

'first string
","

'second string
")"

Since you can't just throw two strings together like that, i would replace the two quotes with the CHR equivalent. Should look something like this:

Cells(j, Dist).Formula = "=IFERROR(" & MidPointE & j & " / " & MidPointE &  CustomerLast & ", "& CHR(034) & CHR(034) & ")"
'pretty sure 034 is the ascii code for "

answered Nov 15, 2017 at 23:26

SuperStew

3,0742 gold badges18 silver badges29 bronze badges

Collectives™ on Stack Overflow

Formula using Variables with VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related