CODEWITHSUNDEEP
excelvba
Richard
Richard

Reputation: 33

Issues with Range.FormulaLocal and textstring in formula

When I finally had figured out that I can use my own language with Range.FormulaLocal in stead of Range.Formula I was very excited!

The possibilities are endless. But, I am encountering a problem with textstrings in formulas.

This code works just fine:

Range("I5").FormulaLocal = "=ALS(A5=1;H5;0)"

But these codelines are not working:

Range("I5").FormulaLocal = "=ALS(A5="x";H5;0)"

Range("I6").FormulaLocal = "=ALS.FOUT(VERT.ZOEKEN(A2;'betaaltermijnen.xlsx'!tabel;3;ONWAAR);"")

Could somebody help me?

Upvotes: 2

Views: 1691

Answers (1)

Wolfie
Wolfie

Reputation: 30046

You're accidentally ending your strings early...

First line: If you have a variable x which you want to include in the string, then use &

Range("I5").FormulaLocal = "=ALS(A5=" & x & ";H5;0)"

If instead you're trying to have the string "x" then you must use an additional quotation mark before each in-string quotation mark. This is called an escape character.

Range("I5").FormulaLocal = "=ALS(A5=""x"";H5;0)"

This way, when VBA sees "", it treats it as the start or end of a quote within a string

By the same reasoning, your second line becomes

Range("I6").FormulaLocal = _
    "=ALS.FOUT(VERT.ZOEKEN(A2;'betaaltermijnen.xlsx'!tabel;3;ONWAAR);"" "") "

Where I've used the _ underscore to continue the line without it getting too long, because the last 6 characters are the important bit!

Upvotes: 3

Related Questions