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!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.