CODEWITHSUNDEEP
excelvbaexcel-formula
Miika OK
Miika OK

Reputation: 60

Insert cell formula from string using code

I have a nested loop that generates a string something like this:

worksheet1!E3;worksheet1!E33;worksheet1!E63;worksheet2!E3;worksheet2!E33;worksheet2!E63;

I need to pass this string to a specified cell as a part of a formula. The final formula should be like: =SUM(<string>).

I get:

Run-time error '1004' Application-defined or object-defined error

My code looks something like this:

Dim query as String
query = "worksheet1!E13;worksheet1!E43;"
    
Worksheets("example").Activate
Range("A1").Formula = "=SUM(" + query + ")"

The error occurs on the last line of the sample code.

Upvotes: 0

Views: 66

Answers (1)

AziMez
AziMez

Reputation: 2072

Hope this help you. it worked for me. Just using Comma instead of Semicolon

Dim query as String
query = "worksheet1!E13,worksheet1!E43"

Worksheets("example").Activate
Range("A1").Formula = "=SUM(" + query + ")"

So, As example, if a user with French regional setting, which have the list separator ; saves a file, then a user with US regional settings that had a list separator , opens the same file, Excel will adjust the French list separators in the formulas automatically.

When writing VBA, though, you will need to use the US-English conventions for the list separator, which is the comma. or you can use FormulaLocal instead.

[EDIT]: Instead, you can use directly the local Formula, using FormulaLocal

As example ( SUM in Frensh formula is SOMME )

Range("A1").FormulaLocal = "=Somme(A2;A5)"

Upvotes: 1

Related Questions