Macro To AutoSum Columns With Rows That Will Vary When The Report Is Run

Hello All,

I was wondering if someone could please help me out. I have spent many a Work Hour now trying to figure this out and I am at Wit's End. I have been "Googling" for a Solution and I just could not find one that would give me exactly something that would work. I have tried many different variations of ActiveCell, Selection, Ranges, in Combination with an xLUp, and I just can't seem to find something that works. I have included pictures of what I am trying to do (PICTURE Here: 1 Formula Example.jpg), along with the piece of Code/Formula I need modified (PICTURE Here: 2 Formula Code.jpg) (I started with one report just to get the "Layout" and then was going back and modifying the Code to make it more dynamic since the Rows would be changing), and a Screen Shot of some Code I thought would work (PICTURE Here: 3 Formula Attempts.jpg, I just can't seem to get the "vRowTop" to reference the Correct Cell.

If someone would be able to help, I would appreciate it very much as I just don't know what else to try. Thank you very much, in advance, for all of your time and help. I really do appreciate it.!!

Hi Macro Fool

Welcome to the Forum!

Please post a file rather than pictures...perhaps one of us can help.

Hi Macro Fool

This code is in the attached...see if it does as you require. Let me know of issues.

Option Explicit

Sub Sub_Tots()
    Dim ws As Worksheet
    Dim myTots As Variant
    Dim myStart As Range
    Dim myEnd As Range
    Dim TopX As String
    Dim TopY As String
    Dim BotX As String
    Dim BotY As String
    Dim FormulaX As String
    Dim FormulaY As String

    Set ws = Sheets("Original")

    For Each myTots In Array("Finance Company Totals:", "Organization Unit Totals :")
        With ws.Columns("C")
            'Find First
            Set myStart = .Find(What:=myTots, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
            TopX = myStart.Offset(0, 1).Address
            TopY = myStart.Offset(0, 2).Address

            'Find Last
            Set myEnd = .Find(What:=myTots, _
                    After:=.Cells(1), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
            BotX = myEnd.Offset(0, 1).Address
            BotY = myEnd.Offset(0, 2).Address
             FormulaX = "=Sum(" & TopX & ":" & BotX & ")"
             FormulaY = "=Sum(" & TopY & ":" & BotY & ")"
            ws.Range(myEnd.Address).Offset(1, 1).Formula = FormulaX
            ws.Range(myEnd.Address).Offset(1, 2).Formula = FormulaY
        End With
    Next myTots
End Sub

Hello jaslake,

HOLY COW, my Friend, HOLY COW!! Your Macro worked PERFECTLY!! Thank you so much for all of your help!! That was the last piece I needed for this Macro and I just was not able to get it to work correctly. You have just saved me from many more hours of frustration.


I can honestly tell you that I apparently have a lot more to learn about Macros because I know I was nowhere close to coming up with the code you wrote. But, I guess we all have to start somewhere. I can also use what you provided as a reference for future uses.

Thank you very much, again, for all of your time and help. I really do appreciate it.

Dan

Hi Macro Fool

You're welcome...glad to help. Please be aware, there's more than one way to skin that "COW"...mine was but one.