SumTotal from dynamic range

[RastaBananaa]

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]

How does VBA know what should be summed?
- 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

Sub addUp1()
    MsgBox WorksheetFunction.Sum(Selection)
End Sub

 

[RastaBananaa]

How does VBA know what should be summed?
- 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

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]

Maybe

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]

Maybe

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]

Glad we could help & thanks for the feedback