I have the following code
For i = 1 To DepRng.Rows.Count
For j = 1 To DepRng.Columns.Count
DepRng.Cells(i, j) = Application.Sum(KidsRng.Row(i)) //Does not work
Next j
Next i
Although I know is wrong, i have no idea how to get it to store in DepRng.Cells(i, j) the total sum of the whole KidsRng.Row[i] Any help?
The following code works ok.
Perhaps you should compare it with yours:
Sub a()
Dim DepRng As Range
Dim kidsrng As Range
Set DepRng = Range("B1:B2")
Set kidsrng = Range("C1:F2")
For i = 1 To DepRng.Rows.Count
DepRng.Cells(i, 1) = Application.Sum(kidsrng.Rows(i))
Next i
End Sub
Just fill the range C1:F2 with numbers and the totals per row will appear in B1:B2 upon execution of the macro.
Sorted, thanks all for ur help
DepRng.Cells(i, j) = Application.Sum(KidsRng.Rows(i)) //just needed to add the "s" in rows
There may be a better way than this, but this is my solution which depends on the internal Excel formula engine though, it might be sufficient enough for what you're doing... It determines the full address of KidsRng.Row(i), and feeds it into a =SUM() formula string and evaluated by Application.Evaluate.
For i = 1 To DepRng.Rows.Count
For j = 1 To DepRng.Columns.Count
DepRng.Cells(i, j).Value = Application.Evaluate("=SUM(" & KidsRng.Row(i).Address(True, True, xlA1, True) & ")")
Next j
Next i
updated it to work if kidsrng existed in a different sheet/book updated to use Application.Evaluate
