SumTotal from dynamic range

RastaBananaa

New Member
Joined
Apr 23, 2018
Messages
8
Hi All,

Facing a problem which (again) I cannot figure out.

I need to SumTotal(or add up using any other function) numbers which could appear in different columns and can have a dynamic range(the numbers needed to be totalled can be anywhere from row 1 - 10,000)

If helps, the numbers will ever only appear in 4 columns; Q, R, S, T though I would like to avoid having a pre-defined location in the code.

So far I have tried cases but when looking for the array which contains the same word, VBA does not know which case to use and thus SumTotal returns 0. The below is the function currently using:

SumTotal = WorksheetFunction.Sum(Range("T2", Range("T2").End(xlDown)))
Range("AC2").Value = SumTotal

Though this function has a pre-defined range which would only work in one of the 4 different sheets.

Hoping someone can help.


Thanks in advance,
RastaBananaa
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,963
Office Version
365
Platform
Windows
How does VBA know what should be summed? :confused:
- which is first cell?
- which is last cell?
- is it only one column every time?


Select any cells and run this macro to return the total
Code:
Sub addUp1()
    MsgBox WorksheetFunction.Sum(Selection)
End Sub
 

RastaBananaa

New Member
Joined
Apr 23, 2018
Messages
8
How does VBA know what should be summed? :confused:
- which is first cell?
- which is last cell?
- is it only one column every time?


Select any cells and run this macro to return the total
Code:
Sub addUp1()
    MsgBox WorksheetFunction.Sum(Selection)
End Sub

Yongle,

Thanks for your time.

This is what I was hoping someone could help me with.

I need to figure out a way to tell VBA which figures to sum up. So I have a column called Turnaways but the location of the column can change, anywhere from column Q - T. Also the number of rows in any of these 4 columns can change.

So the question is, how would I go about telling VBA what to sum

Thanks for your time.

RastaBananaa
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Maybe
Code:
Sub MySum()
   Dim Fnd As Range
   Set Fnd = Range("1:1").Find("Turnaways", , , xlWhole, , , False, False)
   If Not Fnd Is Nothing Then
      MsgBox Application.sum(Range(Fnd.Offset(1), Cells(Rows.Count, Fnd.Column).End(xlUp)))
   End If
End Sub
 

RastaBananaa

New Member
Joined
Apr 23, 2018
Messages
8
Maybe
Code:
Sub MySum()
   Dim Fnd As Range
   Set Fnd = Range("1:1").Find("Turnaways", , , xlWhole, , , False, False)
   If Not Fnd Is Nothing Then
      MsgBox Application.sum(Range(Fnd.Offset(1), Cells(Rows.Count, Fnd.Column).End(xlUp)))
   End If
End Sub

Fluff,

Works like a charm.

Only thing I had to change was the output from MSGBOX to ActiveSheet.Range("AC2") =

Now just need to figure out how to auto fill column X to match column W but I shall make another thread.


Thank you for the help.

RastaBananaa
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,844
Messages
5,386,303
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top