Writing a formula in VBA that checks for blank cells

[Gaetan12345]

Good morning,

i'm writing a simple formula that substract 2 values if a cell is not blank & Excel keeps-on guiving me errors...this seems simple, but i'm not sure where the error is located?

Dim FormuleDonnee As String

FormuleDonnee = "=if($G21<>"";$J21-$G21;"")"

Range("o21").formula = FormuleDonnee
Range("o21").AutoFill Destination:=Range("o21400")

When i use MSGBOX to find out what is the result of this formula, it seems to take away one of the "...on other examples, they say to write 4 ". Still, the error comes back.


Thank's for any help

 

[Gaetan12345]

I finally used different syntax & the formula ias has expected...

FormuleDonnee = "=si(G21<>" & Chr(34) & Chr(34) & ",J21 - G21," & Chr(34) & Chr(34) & ")"

Now, i just have to find a way to execute this formula; the #name ? appears. Once i go on the cell, & press ENTER, it refreshes as expected.

 

[Rick Rothstein]

FormuleDonnee = "=if($G21<>"";$J21-$G21;"")"

You must double up quote marks inside of a text string constant (two internal quote marks stand in for a single quote mark on output), so your above formula should read...

FormuleDonnee = "=if($G21<>"""";$J21-$G21;"""")"

 

[Gaetan12345]

Thank's Rick, the solution you provided works (the same as if i used the chr(34)).

The only thing concerns the refresh; i always get a #NAME ?. If i go to the formula bar, go at the end of my formula & press enter, it refreshes correctly. I've checked the Automatic calculation & still, i have to go in the formula bar & press enter??

Thank's for your reply.

 

[Marcelo Branco]

In VBA you should use , (comma) as argument separator not ; (semicolon)

See if this works

Dim FormuleDonnee As String

FormuleDonnee = "=if($G21<>""""[COLOR=#ff0000],[/COLOR]$J21-$G21[COLOR=#ff0000],[/COLOR]"""")"
 
Range("o21").formula = FormuleDonnee
Range("o21").AutoFill Destination:=Range("o21:o400")

M.

 

[Rick Rothstein]

The only thing concerns the refresh; i always get a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] ?. If i go to the formula bar, go at the end of my formula & press enter, it refreshes correctly. I've checked the Automatic calculation & still, i have to go in the formula bar & press enter??

I notice in your first message, you used the function name IF and in your second message you changed it to SI... one of them is incorrect and if you use that incorrect one, it will generate a #NAME ! error. Is that, perhaps, the source of your problem?

 

[Gaetan12345]

I'm using a french version of excel. I tried the IF statement & for unknown reason, it does it's translation & brings up the formula SI, but this time, working as anticipated in the first time. (finally)

Thank's for you reply

 

[Marcelo Branco]

I'm using a french version of excel. I tried the IF statement & for unknown reason, it does it's translation & brings up the formula SI, but this time, working as anticipated in the first time. (finally)

Thank's for you reply

FYI: As in your Excel (French version) the IF (English) function is SI you can use FormulaLocal in VBA - using exactly the same formula that you use in a worksheet cell (doubling the "" inside the formula string)

Something like

Dim FormuleDonnee As String

FormuleDonnee = "=SI($G21<>"""";$J21-$G21;"""")"

Range("O21").[COLOR=#0000ff]FormulaLocal[/COLOR] = FormuleDonnee
Range("o21").AutoFill Destination:=Range("o21:o400")

M.

 

[Gaetan12345]

Nice Marcelo, i will recall this tought for future formulas.

Thank's