VBA : Error using Range.Formula

Question

I'm using this formula

ThisWorkbook.Sheets("Overview").Range(formrange).Formula = "=IF(OR(ISBLANK(B2);WEEKDAY(DATE($B$38;$B$37;B2);2)>5;DAY(EOMONTH(DATE($B$38;$B$37;B$3);0))<B2);0;IF(C2=""Y"";0,5;1))"

And am having the following error

Run-time error "1004"
Application-denied or object-defined error

Would you guys have an idea what that is?

Replace the semicolons with commas. Formula expects US syntax. — Rory
– Rory, Commented Mar 10, 2016 at 14:12
@chase no the 0,5 in american English would be 0.5, that will need to be changed also, though. So, good catch. — Scott Craner
– Scott Craner, Commented Mar 10, 2016 at 14:19
Weird how international editions of Excel use different function names and punctuation - but formulas entered in VBA still must be in the US format. — dnep
– dnep, Commented Mar 10, 2016 at 14:23
Sure, but it is a brutal violation of least surprise principle. It would be better if cell formulas were always in US format, or could be switched US/local with a click or something. — dnep
– dnep, Commented Mar 10, 2016 at 14:38

Rory · Accepted Answer · 2016-03-10 14:37:25Z

5

The Range.Formula property needs a formula string in the same format it would be entered into a cell on a computer with US regional settings. So for this example you need:

ThisWorkbook.Sheets("Overview").Range(formrange).Formula = "=IF(OR(ISBLANK(B2),WEEKDAY(DATE($B$38,$B$37,B2),2)>5,DAY(EOMONTH(DATE($B$38,$B$37,B$3),0))<B2),0,IF(C2=""Y"",0.5,1))"

answered Mar 10, 2016 at 14:37

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA : Error using Range.Formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related