How to insert, through VBA, a formula in a cell with special characters like "-" or "$"?

Question

I searched on internet, without any help coming out of that... I simply would like to be able to have my VBA code to write this formula in a cell :

=IF(C4="-"; "-"; Cars!C4*C4*Data!$C$8)

As you guessed, there is a page called "Cars" and one called "Data" where I pick the informations needed.

Of course, as it is a VBA code, the C4 will be 2 variables, one for the C and one for the 4 that will evolve... Actually, I tried this :

Worksheets("Calculation").Range(Column & PosStartCalc + 1).Formula = "=" & "IF(" & Column & PosStartCalc & " = " & "" - "" & ";" & " - " & ";" & "Cars!" & Column & PosStart & "*" & Column & PosStartCalc & "*" & "Data!" & "C" & "8" & ")"

(The variable Column contains the column letter and the variable PosStartCalc contains the row number)

This hurts my eyes and apparently VBA's ones too as it gives the error "Run-Time error '13': Type Mismatch' Could anyone tell me how to do that?

Thanks in advance !

Answer 1

Try the following, assuming the column variable is a string and row a long variable. I might not have all the variables right, but you'll be able to get what I meant to do here.

Sub test()

Dim Col As String: Col = "C"
Dim Rw As Long: Rw = 4

With ThisWorkbook.Sheets("Calculation")
    Debug.Print "=IF(" & Col & Rw & "=""-"",""-"",Cars!" & Col & Rw & "*" & Col & Rw & "*Data!$C$8)"
    .Cells(Rw + 1, Col).Formula = "=IF(" & Col & Rw & "=""-"",""-"",Cars!" & Col & Rw & "*" & Col & Rw & "*Data!$C$8)"
End With

End Sub

So what you might forget easily is to use the , as parameter delimiter in a VBA programmed formula. When you put this on your sheet Excel will automatically replace that with the appropriate delimiter for your region.

Another thing to keep in mind; whenever you about to use a string value in such an function, don't forget to wrap it in double quotes!

Don't forget to remove the Debug.print .... line. It was merely there to show the output :)