Stdev with several if conditions?

[Apoca91]

Hello,

Im having a problem regarding a formula im trying to paste. It happens that i need to make a Stdev formula but with mutiple criteria at the same time. And what happens is that, in the way that i have the code right now, after each loop the excel adds "@" extensions to my criteria and i dont know why or if its even possible to overcome this:

Sub TechPrc()

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
Dim userResponse As Integer


    userResponse = MsgBox("Please confirm your option", vbQuestion + vbYesNo)

    If userResponse = vbNo Then
            Exit Sub
    End If

    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 & ")" USED BEFORE THE MACRO EXECUTES THE FORMULA
                
        ElseIf Range("D" & i).Value = "stdev" And Range("I" & i).Value <> Empty Then
       [COLOR=rgb(226, 80, 65)][U][B] Range("J" & i).Value = "=stdev(if(" & str4 & "*" & str5 & "*" & str6 & "," & variable & "))"[/B][/U][/COLOR]
        Range("J" & i).Activate
        ActiveCell.Replace What:="'", Replacement:=""
        
      End If
        
    Next
    
    For i = 15 To 800
        'Check if the cell in column J is not empty
        If Not IsEmpty(Range("J" & i).Value) Then
            Range("J" & i).Copy
            Range("K" & i & ":U" & i).PasteSpecial xlPasteAll
        End If
    Next i
    
    Application.CutCopyMode = False
        
    Range("J15").Activate
    MsgBox "Formulas updated successfully", vbInformation, "Success"
        
End Sub

The problem is in the red line. The value should be something like this:

=STDEV(IF((rd_process[pr.dates]>=J$4)*(rd_process[pr.dates]<=J$5)*(rd_process[pr.Kfeed_flow_kln_1_tph]>=$G52)*(rd_process[pr.Kfeed_flow_kln_1_tph]<=$H52)*(rd_process[pr.Valid_Periods]=1)*(rd_process[pr.UC3_aa.a_00xx.ONOFF]=J$9);rd_process[pr.Kfeed_flow_kln_1_tph]))

Instead of that the excel puts it like this:

=STDEV(IF((rd_process[@[pr.dates]]>=J$4)*(rd_process[@[pr.dates]]<=J$5)*(rd_process[@[pr.Kfeed_flow_kln_1_tph]]>=$G52)*(rd_process[@[pr.Kfeed_flow_kln_1_tph]]<=$H52)*(rd_process[@[pr.Valid_Periods]]=1)*(rd_process[@[pr.UC3_aa.a_00xx.ONOFF]]=J$9);rd_process[pr.Kfeed_flow_kln_1_tph]))

It inserts automatically those characters and i dont know why. The stdev is wrong in this way.

Can anyone give me a hand? Thank you

 

[Domenic]

Try using the FormulaArray property of the Range object instead...

Range("J" & i).FormulaArray = "=stdev(if(" & str4 & "*" & str5 & "*" & str6 & "," & variable & "))"

Hope this helps!

 

[Apoca91]

Try using the FormulaArray property of the Range object instead...

Range("J" & i).FormulaArray = "=stdev(if(" & str4 & "*" & str5 & "*" & str6 & "," & variable & "))"

Hope this helps!

Hi Domenic thank you very much for your help. I modified the code but the following message appears:

Run time erro "1004":

Unable to set the FormulaArray property of the Range class

 

[Domenic]

Try stepping through your code one line at a time by pressing the F8 key until your get to that line. Then enter the following line in the Immediate Window and press the ENTER key...

? "=stdev(if(" & str4 & "*" & str5 & "*" & str6 & "," & variable & "))"

What does it return? Does it return the formula with the correct syntax?