Loop that Sums Ranges Randomly

[ashleybyrdnc]

I need to create a loop that will sum a range of data between each *. Basically what I have is in Column 1 * randomly down the excel sheet, In Column 2 I have data. What I need to be able to do is sum the data that is in between every * in Column 3.

For Ex:
A1 = *
A123 = *
A220 = *

I need a sum at C123 for B1:B123 and also a Sum at C220 for B123:B220 and so on.

Thanks!

 

[SkipVought]

The simplest way would be to MODIFY your table of data to work with the Data > Subtotal feature of Excel.

However, the FIRST issue is that you have OVERLAPPING ranges...
[tt]
1 - 123
123 - 220
[/tt]
So the first step would be to DUPLICATE the rows having asterisks.

Second, each group of rows must have a column containing a unique value that identifies that group.

Once those two issues are solved, using Data > Subtotals takes less than a minute to format and get your subtotals.

Does that sound reaonable to you? Can you handle that?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Just for fun cos I wanted to see how hard it was to do - tbh I kinda agree with Skip but as a means to an end, this sohould give you a start:

Sub GetSum()
Dim fCell As Range
Dim SumStart As Range, SumEnd As Range
Dim lRow As Long

Const strCol = "A"

lRow = Sheets(1).Cells(65536, 1).End(xlUp).Row

With Worksheets(1).Columns(strCol)
    
    Set fCell = .Find("~*", after:=.Range(strCol & lRow), LookIn:=xlValues)
        
        Do
            
            Set SumStart = fCell.Offset(1, 0)
            
            Set fCell = .FindNext(fCell)
            
            Set SumEnd = fCell.Offset(-1, 0)
            
            MsgBox "Range to sum is " & SumStart.Address & " to " & SumEnd.Address
        
        Loop While fCell.Row < lRow

End With

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question