Insert formula in cell using VBA issue

Question

i am having issues inserting the below formula in a cell. I doubled the " already. Any idea what i am doing wrong? Thanks a lot

Sub insert_formulas()

Worksheets("Parsing").Range("H2").Formula = "=IF(E2=""New"";OFFSET(H2,-1;0)-H2;""N/A"")"


End Sub

Replaced it for: (but still error 400)

Sub insert_formulas()

Worksheets("Parsing").Range("H2").Formula = "=IF(E2=""New"",OFFSET(H2,-1,0)-H2,""N/A"")"


End Sub

Its solved. Thanks - the above is correct.

Does inserting the formula directly (without macro) in the cell (of course with single ") works? — Máté Juhász
– Máté Juhász, Commented Jun 5, 2015 at 17:27
Hello, yes it works just fine without VBA. Already replaced with comas just have error 400 still :/ — Gonzalo
– Gonzalo, Commented Jun 5, 2015 at 18:01

Alexander Bell · Accepted Answer · 2015-06-05 18:15:05Z

1

The VBA statement shown in your post (see the one marked with "...the above is correct"):

Worksheets("Parsing").Range("H2").Formula = "=IF(E2=""New"",OFFSET(H2,-1;0)-H2,""N/A"")"

contains error in OFFSET() Function: it should be corrected as following:

Worksheets("Parsing").Range("H2").Formula = "=IF(E2=""New"",OFFSET(H2,-1,0)-H2,""N/A"")"

Best regards,

answered Jun 5, 2015 at 18:15

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

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

2 Comments

Nice catch! Good eye.

Yeah, i realized that after some time, need new glasses! :)