How can I prevent hidden apostrophes when pasting VBA formulas in Excel?

[Apoca91]

Sub TechProc() Dim i As Variant Dim row1 As Variant, rowx As Variant Dim var_low As Variant, var_hig As Variant Dim validPeriods As Variant, UC3OnOff As Variant Dim db As String, variable As String, datedb Dim dateI As String, dateF As String Dim validPeriods_name As Variant Dim UC3OnOff_name As Variant Dim str1 As Variant, str2 As Variant, str3 As Variant Dim str4 As Variant, str5 As Variant, str6 As Variant Dim str As String row1 = 15 rowx = 800 str = ""    For i = row1 To rowx        'Dates criteria        db = "rd_process"        dateI = "J$4"        dateF = "J$5"        datedb = db & "[pr.dates]"        str1 = datedb & ";" & Chr(34) & ">=" & Chr(34) & "&" & dateI & ";" & datedb & ";" & Chr(34) & "<=" & Chr(34) & "&" & dateF        str4 = "(" & datedb & ">=" & dateI & ")*(" & datedb & "<=" & dateF & ")"                'Lower and upper limits for each parameter        var_low = "$G" & i        var_hig = "$H" & i        variable = db & "[" & Range("$E" & i).Value & "]"        str2 = variable & ";" & var_low & ";" & variable & ";" & var_hig        str5 = "(" & variable & ">=" & var_low & ")*(" & variable & "<=" & var_hig & ")"                'Valid Periods and UC3 ON/OFF        validPeriods = 1        validPeriods_name = db & "[pr.Valid_Periods]"        UC3OnOff = "J$9"        UC3OnOff_name = db & "[pr.UC3_aa.a_00xx.ONOFF]"        str3 = validPeriods_name & ";" & validPeriods & ";" & UC3OnOff_name & ";" & UC3OnOff        str6 = "(" & validPeriods_name & "=" & validPeriods & ")*(" & UC3OnOff_name & "=" & UC3OnOff & ")"                If Range("D" & i).Value = "avg" And Range("I" & i).Value <> Empty Then        Range("J" & i).Value = ***"+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"***        Range("J" & i).Activate        ActiveCell.FormulaR1C1 = *"+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"*                        ElseIf Range("D" & i).Value = "stdev" And Range("I" & i).Value <> Empty Then        Range("J" & i).Value = "+stdev(if(" & str4 & "*" & str5 & "*" & str6 & ";" & variable & "))"        Range("J" & i).Activate        ActiveCell.Replace What:="'", Replacement:=""              End If            Next

 

[offthelip]

When writing a formula to the worksheet you must start the string with an equals sign:

Range("J" & i).Value = "=+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"

 

[Apoca91]

When writing a formula to the worksheet you must start the string with an equals sign:

Range("J" & i).Value = "=+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"

Hi thank you first of all for your help.

As it was my second post i didnt fully compiled the code.

Heres a better view:

Sub TechProc()

Dim i As Variant
Dim row1 As Variant, rowx As Variant
Dim var_low As Variant, var_hig As Variant
Dim validPeriods As Variant, UC3OnOff As Variant
Dim db As String, variable As String, datedb
Dim dateI As String, dateF As String
Dim validPeriods_name As Variant
Dim UC3OnOff_name As Variant
Dim str1 As Variant, str2 As Variant, str3 As Variant
Dim str4 As Variant, str5 As Variant, str6 As Variant
Dim str As String


row1 = 15
rowx = 800
str = ""

    For i = row1 To rowx
        'Dates criteria
        db = "rd_process"
        dateI = "J$4"
        dateF = "J$5"
        datedb = db & "[pr.dates]"
        str1 = datedb & ";" & Chr(34) & ">=" & Chr(34) & "&" & dateI & ";" & datedb & ";" & Chr(34) & "<=" & Chr(34) & "&" & dateF
        str4 = "(" & datedb & ">=" & dateI & ")*(" & datedb & "<=" & dateF & ")"
       
        'Lower and upper limits for each parameter
        var_low = "$G" & i
        var_hig = "$H" & i
        variable = db & "[" & Range("$E" & i).Value & "]"
        str2 = variable & ";" & var_low & ";" & variable & ";" & var_hig
        str5 = "(" & variable & ">=" & var_low & ")*(" & variable & "<=" & var_hig & ")"
       
        'Valid Periods and UC3 ON/OFF
        validPeriods = 1
        validPeriods_name = db & "[pr.Valid_Periods]"
        UC3OnOff = "J$9"
        UC3OnOff_name = db & "[pr.UC3_aa.a_00xx.ONOFF]"
        str3 = validPeriods_name & ";" & validPeriods & ";" & UC3OnOff_name & ";" & UC3OnOff
        str6 = "(" & validPeriods_name & "=" & validPeriods & ")*(" & UC3OnOff_name & "=" & UC3OnOff & ")"
       
        If Range("D" & i).Value = "avg" And Range("I" & i).Value <> Empty Then
        Range("J" & i).Value = "+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"
        Range("J" & i).Activate
        ActiveCell.FormulaR1C1 = "+AverageIfs(" & variable & ";" & str1 & ";" & str2 & ";" & str3 & ")"
               
        ElseIf Range("D" & i).Value = "stdev" And Range("I" & i).Value <> Empty Then
        Range("J" & i).Value = "+stdev(if(" & str4 & "*" & str5 & "*" & str6 & ";" & variable & "))"
        Range("J" & i).Activate
        ActiveCell.Replace What:="'", Replacement:=""
       
      End If
       
    Next

       
End Sub

I want to paste those formulas to the respective cell, the thing is when i paste it, Excel places a hidden apostrophe. I really dont know how i can solve this problem.

If i start the formula with an = sign it will give me an error " Run time error '1004' application defined or object defined error"

 

[RoryA]

Use commas, not semicolons, in the formula strings. VBA defaults to US regional settings unless told otherwise.

 

[Apoca91]

Use commas, not semicolons, in the formula strings. VBA defaults to US regional settings unless told otherwise.

Thank you Rory.

I tried this: "+AverageIfs(" & variable & "," & str1 & "," & str2 & "," & str3 & ")"

But the resault came the same. It placed it like it was a text with a hidden apostrophe instead of making the formula

 

[RoryA]

I just noticed you are also using semicolons in the text assigned to the variables - did you change that too?

 

[Apoca91]

I just noticed you are also using semicolons in the text assigned to the variables - did you change that too?

Ive changed it right now. It executes the macro but again, places it like text with the hidden apostrophe. If i want the formula to work i need to remove it from the formula bar.

 

[RoryA]

Once you have changed all of the semicolons and used:

Range("J" & i).Formula= "=AverageIfs(" & variable & "," & str1 & "," & str2 & "," & str3 & ")"

what is the exact result in the cell? Please copy and paste it here.

 

[Apoca91]

Once you have changed all of the semicolons and used:

Range("J" & i).Formula= "=AverageIfs(" & variable & "," & str1 & "," & str2 & "," & str3 & ")"

what is the exact result in the cell? Please copy and paste it here.

its this:

'+AverageIfs(rd_process[pr.CO_ctnt_stack_1_mgNm3],rd_process[pr.dates],">="&J$4,rd_process[pr.dates],"<="&J$5,rd_process[pr.CO_ctnt_stack_1_mgNm3],$G15,rd_process[pr.CO_ctnt_stack_1_mgNm3],$H15,rd_process[pr.Valid_Periods],1,rd_process[pr.UC3_aa.a_00xx.ONOFF],J$9)

 

[offthelip]

If i start the formula with an = sign it will give me an error " Run time error '1004' application defined or object defined error"

I find the easiest way to find out what the error is when I am writing formula to the worksheet with vba is to leave out the equals sign ( as you orginally did) then the vba just writes a string to the cells ( as you discovered) now go to the cell and update it and put the equals sign back in, excel will then give a very good hint as to what the problem. VBA error message is because the formuila has an error in it.