HELP GETTING SUM OF COLUMNS IN A RANGE

[ytjjjtyj]

Hello, So I have a named range called "Hello" with dimensions C9F108.
I have another named range called "Hello1" with dimensions C112F211.
My worksheet for this is called WkshtBal.

For both of the named ranges, I want the sum of each column in the named range to appear in the row below the named range ( Row 109 for Hello) and in the same respective columns.

I then want to divide the sum of the columns of "Hello1" by the sum of columns of "Hello" to start at cell C7.

Anyone know how to do this in VBA Subroutine?

[Kenneth Hobs]

Sub Main()  
  Dim ws As Worksheet, rH As Range, rH1 As Range, col As Range
  
  Set ws = Worksheets("WkshtBal")
  Set rH = Range("Hello")
  Set rH1 = Range("Hello1")


  For Each col In rH.Columns
    'ws.Cells(rH.Rows.Count + rH.Row, col.Column) = WorksheetFunction.Sum(col)
    ws.Cells(rH.Rows.Count + rH.Row, col.Column).Formula = "=Sum(" & col.Address & ")"
  Next col
  
  For Each col In rH1.Columns
    'ws.Cells(rH1.Rows.Count + rH1.Row, col.Column) = WorksheetFunction.Sum(col)
    ws.Cells(rH1.Rows.Count + rH1.Row, col.Column).Formula = "=Sum(" & col.Address & ")"
  Next col
  
  'ws.Range("C7") = WorksheetFunction.Sum(rH1) / WorksheetFunction.Sum(rH)
  ws.Range("C7").Formula = "=Sum(" & rH1.Address & ":" & rH.Address & ")"
End Sub

[ytjjjtyj]

Hi Kenneth, thank you. Everything worked except when I divide- it didn't work. I need to divide the respective columns of the sums obtained for each column in Hello1 by the same columns that were summed in Hello. So from C7: DF7 - it would have the values of the sum of Hello1 / sum of Hello columns.

[Kenneth Hobs]

I think you meant C7:F7 in #3. I did not code it for jagged ranges. As such, it could go something like:

Sub Main()  
  Dim ws As Worksheet, rH As Range, rH1 As Range, col As Range
  Dim c As Range, i As Integer, a
  
  Set ws = Worksheets("WkshtBal")
  Set rH = Range("Hello")
  Set rH1 = Range("Hello1")
  ReDim a(1 To rH.Cells.Count)


  For Each col In rH.Columns
    Set c = ws.Cells(rH.Rows.Count + rH.Row, col.Column)
    c.Formula = "=Sum(" & col.Address & ")"
    i = i + 1
    a(i) = c.Address & ":"
  Next col
  
  i = 0
  For Each col In rH1.Columns
    Set c = ws.Cells(rH1.Rows.Count + rH1.Row, col.Column)
    c.Formula = "=Sum(" & col.Address & ")"
    i = i + 1
    ws.Cells(7, col.Column).Formula = "=Sum(" & a(i) & c.Address & ")"
  Next col
End Sub

[ytjjjtyj]

Thank you!