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

 

[Apoca91]

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.

Im trying to understand why does Excel thinks that what im trying to copy is a string of text instead of a formula. Is it possible to simulate before the paste, a double click on the active cell so that the "paste" process will occurr inside the cell?

Right now the only thing i need to do is to remove the apostrophe. the problem is sometimes i have over 800 lines, and this process is time consuming

 

[RoryA]

its this:

I don't see how it could be that when there is no + in the code that I posted.

 

[Apoca91]

I don't see how it could be that when there is no + in the code that I posted.

Youre right sorry.

It works now. Apparently i had to change the ";" to "," like you said in everything and also change the "+" to "=" at the beggining of the formula. Thank you very much for you guys help!!