WorksheetFunction SumIfs with multiple arguments doesn't return consistent results

[marimar02]

Hello,

I've been trying to find a solution before resorting to asking the community. I am learning VBA without selecting/activating cells (when not required) and without using excel formulas (unless absolutely necessary) and would like this to be a WorksheetFunction rather than a "FormulaR1C1".

This VBA code yesterday gave me different results than it did today and it has to do with Arguments 4 & 5 in which latter is simply a value "Complete". I added these arguments after testing it without. But I want the code to sum a range where results are marked as completed, too.

Sub Populate_WL_Table()

Dim sSht As Worksheet, tSht As Worksheet
Dim sRng As Range, sRng2 As Range, tRng As Range
Dim sLastRow As Long, tLastRow As Long
Dim i As Integer, x As Integer
Dim Arg1 As Range, Arg2 As Range, Arg3 As Variant, Arg4 As Range, Arg5 As Variant

's = source
't = target

Set sSht = Sheets("Data History")
Set tSht = Sheets("Weekly Data")

sSht.Activate

sLastRow = sSht.Cells(sSht.Rows.Count, "G").End(xlUp).Row

tSht.Activate

tLastRow = tSht.Cells(tSht.Rows.Count, "H").End(xlUp).Row

Set Arg1 = sSht.Range("M2:M" & sLastRow) 'Sum Range
Set Arg2 = sSht.Range("A2:A" & sLastRow) '1st Argument Range
Set Arg4 = sSht.Range("G2:G" & sLastRow) '2nd Argument Range
Set Arg5 = tSht.Range("S1") '2nd Argument Criteria which is word "Complete"

    For i = 2 To tLastRow
        If tSht.Range("H" & i) <> "" Then 'skips rows where Range in H Column doesn't contain a value
            Set Arg3 = tSht.Range("H" & i) '1st Argument Criteria
            tSht.Cells(i, 6).Value = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3, Arg4, Arg5.Value)
        End If
    Next i

Set sSht = Nothing
Set tSht = Nothing
Set sRng = Nothing
Set tRng = Nothing

End Sub

Thank you and please let me know if I can clarify anything...

 

[marimar02]

Just type it into the code as "Complete" in place of Arg5.Value (or whichever Arg it was).

In some cases, vba is case sensitive but I don't think that will be the problem you are encountering.

Hardware / software wouldn't make a difference unless you have a corrupt installation, but if that was the case you would have bigger problems than the occasion incorrect sumifs result.

I would highly suspect that the cause of the anomaly is the data that you are working with more than anything else.

The use of RAND() or NOW() in formulas could be one possible cause.

Thank you. I'll change the code to "Complete" instead of cell reference. I'll keep monitoring the issue and if it persists, I'll have to go back to R1C1 formulas. Thanks again.