PDA

View Full Version : [SOLVED] HELP GETTING SUM OF COLUMNS IN A RANGE



ytjjjtyj
08-01-2019, 06:24 AM
Hello, So I have a named range called "Hello" with dimensions C9:DF108.
I have another named range called "Hello1" with dimensions C112:DF211.
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
08-01-2019, 07:47 AM
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
08-01-2019, 10:10 AM
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
08-01-2019, 12:41 PM
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
08-02-2019, 12:41 PM
Thank you!