It is not possible to insert a formula saved inside VBA as a string variable into a cell

Asked May 28 '20 at 20:38

Active May 28 '20 at 20:38

Viewed 39 times

I have created a big formula as a string variable inside VBA. Then when I am trying to assign this formula as a value to a cell I run across the following error '1004 - Application-defined or object-defined error". Is there a way to insert my formula into a cell? Would highly appreciate any help!

Sub insertFormula()


Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim strForm As String
Dim i As Long


ws.Range("A2").Value = "=VLOOKUP(O6; SQLTable; 2; 0)*P6"

strForm = ws.Range("A2").Value

For i = 7 To Worksheets("Sheet1").Range("O" & Rows.Count).End(xlUp).Row

    strForm = strForm & "+VLOOKUP(" & ws.Cells(i, 15).Address(False, False) & "; SQLTable; 2; 0)*" & ws.Cells(i, 16).Address(False, False)

Next i

ws.Range("A2").Value = strForm

End Sub

edited May 28 '20 at 20:38

braX

11,506
5
20
33

asked May 28 '20 at 20:38

Max Hamesoul

How many characters are in the formula? – braX May 28 '20 at 20:39
one applies a formula by using `.Formula` not `.Value` and one must use US-EN nomenclature, change the `;` to `,` – Scott Craner May 28 '20 at 20:40
@braX Not really many. 5 VLOOKUP formulas. The problem arises when I insert "=" at the beginning of the string, which I am trying to pass as a value to a cell. – Max Hamesoul May 28 '20 at 20:42
Before assigning it to a cell, print the string and verify that it looks correct and is in EN-US format. – Scott Craner May 28 '20 at 20:43
@Scott Craner Output looks correctly. As I am assigning it as a string I used format that is used by my version of excel. – Max Hamesoul May 28 '20 at 20:48
Can you post the output as text in the post – Scott Craner May 28 '20 at 20:50
It looks this way: `=-60+VLOOKUP(O7; SQLTable; 2; 0)*P7+VLOOKUP(O8; SQLTable; 2; 0)*P8` – Max Hamesoul May 28 '20 at 20:53
1

`.Formula` *requires* the `,` instead of `;` - see [this](https://stackoverflow.com/questions/35724156/different-languages-issue-when-inserting-formula-from-vba). – BigBen May 28 '20 at 22:18

It is not possible to insert a formula saved inside VBA as a string variable into a cell

0 Answers0