Writing a formula with concatenated parts into a cell

Question

Scenario: I have a code that should write a formula to a worksheet cells. This formula is for an API to retrieve some value. My formula is inside a loop (this is done for multiple columns) and references the first row for an identifier.

The original formula:

=FS(B1;"FI(DATE,,DATE)")

The modified formula with the floating reference (inside the loop):

For i = 1 To lColumn
    If wb.Worksheets("Dates").Cells(i, 1).Value <> "" Then
        wb.Worksheets("Dates").Cells(i,2).value = "=FS(" & i & "1;"FI(DATE,,DATE)")"
    End If
Next i

Where lColumn is some pre-defined number.

Issue: I keep getting the "Unexpected end of statement" error in the formula part of the loop.

What I already tried: I tried different variations, repositioning the "s and 's, for example:

wb.Worksheets("Dates").Cells(i,2).value = "'"=FS(" & i & "1;"FI(DATE,,DATE)")""

or

wb.Worksheets("Dates").Cells(i,2).value = "'=FS(" & i & "1;"FI(DATE,,DATE)")"

or

wb.Worksheets("Dates").Cells(i,2).value = "'""=FS(" & i & "1;"FI(DATE,,DATE)")"

and so on. But the error still persists.

Question: What is the proper way to do this operation?

Can you take a look here and try to do the steps - https://stackoverflow.com/questions/49363434/excel-vba-formula-german-french-italian-russian-dutch-foreign-function/49363501#49363501 — Vityata, Jul 18 '18 at 10:00

FunThomas · Accepted Answer · 2018-07-18T10:29:38.470

Working with formulas in VBA is a little bit tricky:

To write a formula, use the range.formula property, not the .value.
You have to write the formula as if you are using an english Excel. Parameter-separator is comma (not semicolon).
If a formula needs a quote, double it so that the VBA compiler understands that you want a quote within a string.
I find it helpfull to write a formula into a variable before assigning it - you can check in the debugger if it is exactly how it should before assigning it.
To check how the formula should look like, write it into a cell, change to the VBA-editor, open the immediate window and write ? activecell.formula

Try (untested as the formula you need is not valid to us):

with wb.Worksheets("Dates")
  dim f as string, adr as string
  adr = cells(i, 1).address(false, false)   ' get rid of Dollar signs
  f = "=FS(" & adr & ",""FI(DATE,,DATE)"")"
  .Cells(i, 2).formula = f
end with

I think it should be `adr = cells(1, i).address(false, false)` as it's the column that's changing, not the row — 3-14159265358979323846264, Jul 18 '18 at 10:26

3-14159265358979323846264 · Answer 2 · 2018-07-18T10:21:16.090

1

wb.Worksheets("Dates").Cells(i,2).formula = "=FS(" & Cells(1, i).Address(0,0) & ";""FI(DATE,,DATE)"")"

There may be a better way to convert the column number to a letter (which is the problem you are having, along with the double quotes)!

edited Jul 18 '18 at 10:21

answered Jul 18 '18 at 10:00

3-14159265358979323846264

3,250
2
15
38

Instead of using replace, consider using the parameters of `Address` to return a relative reference (ie without `$`'s) – chris neilsen Jul 18 '18 at 10:14
Thanks @chrisneilsen, wasn't aware of that. Will update my answer :0) – 3-14159265358979323846264 Jul 18 '18 at 10:18
Do not write a formula into `.value` - use `.formula` instead – FunThomas Jul 18 '18 at 10:19
@FunThomas. You are quite right. I was focusing on the right hand side far too much! – 3-14159265358979323846264 Jul 18 '18 at 10:20

Writing a formula with concatenated parts into a cell

2 Answers2

Linked