Summing up user defined results

[roadie.girl]

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!!!

 

[Bernie Deitrick]

You would be much better off posting an example of your data table, with the results that you would
like to see based on the sample.

HTH,
Bernie
MS Excel MVP

 

[roadie.girl]

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 selects row A and row C, it should appear as
oil: 157
gas: 257
water: 33

i can't use the status bar to do this, because as far as my knowedge
takes me, it just sums all of these numbers up into one lump sum.

thanks,
rebekah

 

[Bernie Deitrick]

rebekah,

Try this version:

Sub Button6_Click()

Dim myArea As Range
Dim myCell As Range
Dim i As Integer
Dim ColStart As Integer
Dim ColCount As Integer
Dim RowStart As Integer
Dim myVals(1 To 255) As Double

For Each myArea In Selection
For Each myCell In Intersect(myArea.EntireRow, myArea.CurrentRegion)
If IsNumeric(myCell.Value) Then
myVals(myCell.Column) = myVals(myCell.Column) + myCell.Value
End If
Next myCell
Next myArea

ColStart = Selection.CurrentRegion.Cells(1, 1).Column
ColCount = Selection.CurrentRegion.Columns.Count
RowStart = Selection.CurrentRegion.Cells(1, 1).Row
For i = ColStart + 1 To ColStart + ColCount - 1
MsgBox "The total " & Cells(RowStart, i).Value & " is " & myVals(i)
Next i

End Sub

 

[roadie.girl]

thanks so much Bernie - that's definitely what i needed and it works
wonderfully! thanks again!, rebekah