Consulting

Results 1 to 5 of 5

Thread: HELP GETTING SUM OF COLUMNS IN A RANGE

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    45
    Location

    HELP GETTING SUM OF COLUMNS IN A RANGE

    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?

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    45
    Location
    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.

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    45
    Location
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •