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

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!!
>

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

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


--
HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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