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.
Thank you and please let me know if I can clarify anything...
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.
VBA Code:
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 SubThank you and please let me know if I can clarify anything...
