Summing user selected records

[rmullen]

Hi all - I'm new to VBA programming in Excel and so any help i'd
totally love!

I'm currently writing a function.
Objective: user can select rows (do not have to be sequential). User
clicks on button. UserForm appears with summed results from ONLY rows
that he selected.

What I have now, well it doesn't work:

Sub Button6_Click()
Dim i As Integer
Dim totalNumbers As Integer
Dim aRange As range

For Each a In Selection.Areas
'MsgBox "Area " & i & " of the selection contains " & _
' a.Rows.Count & " rows."
'call with the selection area and then number of rows
Call SumValues(a, a.Rows.Count)
i = i + 1
Next a
End Sub

Public Sub SumValues(a As AcRecord, numberOfRows As Integer)

Dim i As Integer
Dim iRow As Integer

iRow = 6

'set up the column headings

'loop through the recordset

Do While rs.EOF = False
i = 1

If Oil = 1 Then
Call printOrNot("Oil", rs!Oil, i, iRow)
i = i
End If

If Gas = 1 Then
Call printOrNot("Gas", rs!MCFs, i, iRow)
i = i
End If

If Water = 1 Then
Call printOrNot("Water", rs!Water, i, iRow)
i = i
End If
Loop
End Sub

Public Sub printOrNot(ByRef sumVal As String, rsName As String, ByRef i
As Integer, iRow As Integer)
If iRow = 6 Then
'objSheet.Cells(iRow, i) = colHeading
Else
sumVal = sumVal + rsName
End If
'return this value
End Sub


My spreadsheet has any number of columns as seen in SumValues()
function. So whenever the user selects rows, I just need to sum all of
the columns up on the spreadsheet that he asked to see.

Any help that you guys can give would be so totally appreciated. Thank
you so much in advance!!!

[Tom Ogilvy]

Why not make the status bar visible and look at the displayed sum in the
lower right portion.

--
Regards,
Tom Ogilvy


"rmullen" <[email protected]> wrote in message
news:[email protected]...
> Hi all - I'm new to VBA programming in Excel and so any help i'd
> totally love!
>
> I'm currently writing a function.
> Objective: user can select rows (do not have to be sequential). User
> clicks on button. UserForm appears with summed results from ONLY rows
> that he selected.
>
> What I have now, well it doesn't work:
>
> Sub Button6_Click()
> Dim i As Integer
> Dim totalNumbers As Integer
> Dim aRange As range
>
> For Each a In Selection.Areas
> 'MsgBox "Area " & i & " of the selection contains " & _
> ' a.Rows.Count & " rows."
> 'call with the selection area and then number of rows
> Call SumValues(a, a.Rows.Count)
> i = i + 1
> Next a
> End Sub
>
> Public Sub SumValues(a As AcRecord, numberOfRows As Integer)
>
> Dim i As Integer
> Dim iRow As Integer
>
> iRow = 6
>
> 'set up the column headings
>
> 'loop through the recordset
>
> Do While rs.EOF = False
> i = 1
>
> If Oil = 1 Then
> Call printOrNot("Oil", rs!Oil, i, iRow)
> i = i
> End If
>
> If Gas = 1 Then
> Call printOrNot("Gas", rs!MCFs, i, iRow)
> i = i
> End If
>
> If Water = 1 Then
> Call printOrNot("Water", rs!Water, i, iRow)
> i = i
> End If
> Loop
> End Sub
>
> Public Sub printOrNot(ByRef sumVal As String, rsName As String, ByRef i
> As Integer, iRow As Integer)
> If iRow = 6 Then
> 'objSheet.Cells(iRow, i) = colHeading
> Else
> sumVal = sumVal + rsName
> End If
> 'return this value
> End Sub
>
>
> My spreadsheet has any number of columns as seen in SumValues()
> function. So whenever the user selects rows, I just need to sum all of
> the columns up on the spreadsheet that he asked to see.
>
> Any help that you guys can give would be so totally appreciated. Thank
> you so much in advance!!!
>

[rmullen]

I'm not sure what you mean exactly -
Pretty much, I'm just trying to figure out how to sum up columns from a
randomly selected area (by the user).
Displaying it (i'm assuming) will be the easier part - I just can't
figure out how to figure out which records are selected by the user,
and then to sum those up ....

[Tom Ogilvy]

> I'm not sure what you mean exactly -

that is what I mean. You don't need to figure it out. Excel already
automatically provides that information.

However, if you want to know what is selected, then use the selecton object.

If you want to know what the sum of the selected cells is:

msgbox "Sum of selection is " & application.sum(selection)

--
Regards,
Tom Ogilvy

"rmullen" <[email protected]> wrote in message
news:[email protected]...
> I'm not sure what you mean exactly -
> Pretty much, I'm just trying to figure out how to sum up columns from a
> randomly selected area (by the user).
> Displaying it (i'm assuming) will be the easier part - I just can't
> figure out how to figure out which records are selected by the user,
> and then to sum those up ....
>

[rmullen]

ok - i know what you mean now by the status bar.
the problem with using the status bar is that i'm summing up different
columns. excel likes to group the entire selection into one sum (as far
as my knowledge tells me anyway)

sample dataset
---------------------------------
row --- name ----- oil ----- gas -----
water
A Church Creek 50 100 20
B Gulch 25 200 17
C Cherry 57 157 13

so if my user selectes row A and C .... oil sum should be 107, gas sum
should be 257, water sum should be 33 ...
thanks so much for your help

[Tom Ogilvy]

for i = 3 to 5
msg = msg & cells(1,i) & ": " & application.Sum(Intersect( _
columns(i),Selection.entireRow)) & vbNewline
Next
msgbox msg

--
Regards,
Tom Ogilvy


"rmullen" <[email protected]> wrote in message
news:[email protected]...
> ok - i know what you mean now by the status bar.
> the problem with using the status bar is that i'm summing up different
> columns. excel likes to group the entire selection into one sum (as far
> as my knowledge tells me anyway)
>
> sample dataset
> ---------------------------------
> row --- name ----- oil ----- gas -----
> water
> A Church Creek 50 100 20
> B Gulch 25 200 17
> C Cherry 57 157 13
>
> so if my user selectes row A and C .... oil sum should be 107, gas sum
> should be 257, water sum should be 33 ...
> thanks so much for your help
>

[rmullen]

Thank you very much! it works great - i never knew about the intersect
function before - definitely is something i shall read up on a lot.
thanks again!