.Formula brings error with punctuation marks vba

Question

I have a problem with adding formula to a cell through VBA. Everything is ok till the moment when I add punctuation mark like "(" to it. What I'm doing wrong here? I've tried already using chr() function and it doesn't work.

 Do Until ws.Cells(i, 1) = ""
 If ws.Cells(i, 7).Value <> "" Then
    If ws.Cells(i, 7).Value <> 0 Then
        ws.Cells(i, 7) = 200
        ws.Cells(i, 8).Value = 0
        ws.Cells(i, 9).Value = 0
        ws.Range("J" & i).Formula = "=IF(H" & i & "-F" & i & "<=0;0;H" & i & "-F" & i & ")"

Regards, Ukalo

BigBen formula should be "=IF(H5-F5<=0;0;H5-F5)" of course with actual row number instead of 5 — Lukasz, Oct 02 '19 at 11:56
oh,sorrry. Run-time error 1004 / Application-defined or object defined error — Lukasz, Oct 02 '19 at 12:01
Which line produces the error? Did you initialize and declare `ws` correctly? — riskypenguin, Oct 02 '19 at 12:02

score 2 · Answer 1 · answered Oct 02 '19 at 12:01

2

Replace:

ws.Range("J" & i).Formula = "=IF(H" & i & "-F" & i & "<=0;0;H" & i & "-F" & i & ")"

with:

ws.Range("J" & i).Formula = "=IF(H" & i & "-F" & i & "<=0,0,H" & i & "-F" & i & ")"

If you want to make it easy, see:

Reference

answered Oct 02 '19 at 12:01

Gary's Student

95,722
10
59
99

Yep, that's right. I wouldn't track that by myself. Thank you Gary's Student. Do you know if it's connected to local set up of excel? And should I in other functions change is as well from ";" to " ' " ? – Lukasz Oct 02 '19 at 12:05
@Lukasz Using `FornulaLocal` make it easier to copy formulas from the worksheet to VBA code. – Gary's Student Oct 02 '19 at 12:08

.Formula brings error with punctuation marks vba

1 Answers1