User friendly summarising

**murphybrendan** · 08-12-2010, 05:53 AM

Hi Guys,

I'd be a moderate user in the sense that I have written some VBA codes but I have a tendancy to copy and paste, editing to my needs. This one has me stumped though.

I've an excel document attached. Basically theres an array of data (in the data tab - simplified here). On the summary tab the user can enter either of 2 inputs, each with a seperate macro.

For input one the user enters a start row (X) and an end row (Y). The Macro then sums up the column to the right of this from rows X to Y-1, i.e. sum the number corresponding to row X down to the number correspoiding to the row above row Y and return the value.

For input two its sort of the opposite. The user enters an end row (Y) and a number. The Macro then sums all numbers from row Y-1 backwards (i.e. Y-1, Y-2, Y-3 etc...) until it reaches a value greater than the inputted number, then returns the corresponding row number.

Any help would be much appreciated. If I got the basic code for the example attached I can edit it and implement it to my data set.

Thanks
Brendan

**teylyn** · 08-12-2010, 06:08 AM

hello,

the formula for your first problem could be

=SUM(INDEX(Data!$B:$B,MATCH(Summary!C3,Data!$A:$A,0)):INDEX(Data!$B:$B,MATCH(Summary!$C$4,Data!$A:$A,0)-1))

I don't quite see what you want to achieve with the second problem. None of the data matches what you have in your description. Please explain exactly which cells to sum.

Offsetting upwards from the row labelled M, the value of 38 is achieved at row labelled F, not E. Please explain.

**StephenR** · 08-12-2010, 06:19 AM

Rather tortuous, but I think it gets you there.

Sub x()

Dim r As Long, r1 As Long, r2 As Long

Sheets("Summary").Activate

With Application
    If Range("C3") <> "" And Range("C4") <> "" Then
        r1 = .Match(Range("C3"), Sheets("Data").Columns(1), 0)
        r2 = .Match(Range("C4"), Sheets("Data").Columns(1), 0)
        Range("C6") = .Sum(Sheets("Data").Range("B1").Offset(r1 - 1).Resize(r2 - r1))
        Exit Sub
    End If
    
    If Range("C11") <> "" And Range("C12") <> "" Then
        r1 = .Match(Range("C11"), Sheets("Data").Columns(1), 0) - 1
        r2 = 1
        Do Until .Sum(Sheets("Data").Range("B1").Offset(r1 - r2).Resize(r2)) >= Range("C12")
            r2 = r2 + 1
        Loop
        Range("C14") = .Index(Sheets("Data").Columns(1), r1 - r2 + 1)
        Exit Sub
    End If
End With

MsgBox "Not enough info"

End Sub

teylyn: in the second method you start from L, not M. I see your solution will probably be two lines long...

**murphybrendan** · 08-12-2010, 06:54 AM

Thanks Guys. Both these worked. I've went with the VBA code. My data base is quite large, its easier to understand editing the code for me than formula. Thanks to both of you for the speedy inputs. Much Appreciated!!!