"unknow runtime error" when using VBS to set formula to a cell

Asked May 25 '22 at 18:04

Active May 25 '22 at 18:04

Viewed 22 times

I've been using Visual Basic Scripting Edition (VBS) for excel automation without much of a problem lately, however, when trying to set the value of a specific cell, programmatically, to this formula =IF(C3<IFERROR(VLOOKUP(<some_value>;A:D;4;FALSE);<some_value>);0;1) I keep getting the following error:

I've noted that a runtime error is raised when you try using VBS to assign a broken formula to a cell, however, when I tried to write that formula directly to excel it works perfectly.

Here's the snippet that I used when trying to do it by VBS:

AffiliatedWB.Worksheets("COLETA").Cells(IndicativeIndex, 5).Value = EvaluateGoal(CurrentGoal, IndicativeIndex)
' I also tried with:
AffiliatedWB.Worksheets("COLETA").Cells(IndicativeIndex, 5).Formula = EvaluateGoal(CurrentGoal, IndicativeIndex)

Both ways don't work. And yes, I checked the EvaluateGoal Function. It works perfectly and returns the formula string without any errors.

Any thoughts on a workaround?

asked May 25 '22 at 18:04

Milky

2

Try `.FormulaLocal` – BigBen May 25 '22 at 18:05
1

@BigBen it worked, thanks!!! Any explanation why, so I can close the question – Milky May 25 '22 at 18:10
The `;` are the issue. `.Formula` expects `,` as the separator, as discussed in the linked thread. – BigBen May 25 '22 at 18:11

"unknow runtime error" when using VBS to set formula to a cell

0 Answers0