Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
Why not make the status bar visible and look at the displayed sum in the
lower right portion. -- Regards, Tom Ogilvy "rmullen" wrote in message ps.com... 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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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 .... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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" wrote in message ups.com... 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 .... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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 :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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" wrote in message ups.com... 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 :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing user selected records
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing data on duplicate records? | Excel Worksheet Functions | |||
Summing up Unique Records | Excel Discussion (Misc queries) | |||
Trouble identifying selected records | Excel Discussion (Misc queries) | |||
Summing the # of records | New Users to Excel | |||
Summing the last records of every month. | Excel Programming |