Inserting formula as string into a cell

Question

It seems to be a problem with some characters in it

My original formula in the worksheet (working):

ThisWorkbook.Worksheets("Zazmluvnenia").Cells("3", "AM").Value = "=Výkony!M4 & ", " & TEXT(Výkony!J4;"d.m.yyyy") & ", " & TEXT(Výkony!K4;"hh:mm")"

Putting this into VBA doesn't work because of the quotes. I tried to solve the problem this way: How do I put double quotes in a string in vba?

I changed my code according to the answers on the question above:

"=Výkony!M3 & " & Chr(34) & ", " & Chr(34) & " & TEXT(Výkony!J3;" & Chr(34) & "d.m.yyyy" & Chr(34) & ") & " & Chr(34) & ", " & Chr(34) & " & TEXT(Výkony!K3;" & Chr(34) & "hh:mm" & Chr(34) & ")"

And error '1004' occurred.

Answer 1

Double Quotes vs CHR(34)

• Your CHR solution was almost fine, but you didn't know that VBA doesn't recognize the semi-colon (;) as a separator like Excel does. You always have to replace every semi-colon (used as a separator) with a comma in VBA.
• Similarly VBA uses the dot (.) as the decimal separator.

The Code

Option Explicit

Sub SEMI()

    Dim strF As String
    
    ' Double double-quotes ("") in several rows.
    strF = "=Výkony!M4&"", """ _
         & "&TEXT(Výkony!J4,""d.m.yyyy"")&"", """ _
         & "&TEXT(Výkony!K4,""hh:mm"")"
    
    ' CHR(34) in several rows.
    strF = "=Výkony!M4&" & Chr(34) & ", " & Chr(34) _
         & "&TEXT(Výkony!J4," & Chr(34) & "d.m.yyyy" & Chr(34) & ")&" _
         & Chr(34) & ", " & Chr(34) _
         & "&TEXT(Výkony!K4," & Chr(34) & "hh:mm" & Chr(34) & ")"
    
    ' Double double-quotes ("") in one row.
    strF = "=Výkony!M4&"", ""&TEXT(Výkony!J4,""d.m.yyyy"")&"", ""&TEXT(Výkony!K4,""hh:mm"")"
    
    ' CHR(34) in one row.
    strF = "=Výkony!M4&" & Chr(34) & ", " & Chr(34) & "&TEXT(Výkony!J4," & Chr(34) & "d.m.yyyy" & Chr(34) & ")&" & Chr(34) & ", " & Chr(34) & "&TEXT(Výkony!K4," & Chr(34) & "hh:mm" & Chr(34) & ")"
    
    ThisWorkbook.Worksheets("Zazmluvnenia").Cells("3", "AM").Value = strF

End Sub