Excel-VBA Named range row sum

Question

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?

Answer 1

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.

Answer 2

Sorted, thanks all for ur help

   DepRng.Cells(i, j) = Application.Sum(KidsRng.Rows(i)) //just needed to add the "s" in rows

Answer 3

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