Summing up user defined results

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

0
12/8/2005 6:08:32 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1619 Views

Similar Articles

[PageSpeed] 55

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@gmail.com> wrote in message 
news:1134065312.223333.103540@f14g2000cwb.googlegroups.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!!!
> 


0
Bernie
12/8/2005 6:27:57 PM
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

0
12/8/2005 7:06:24 PM
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


<roadie.girl@gmail.com> wrote in message 
news:1134068784.142772.218140@o13g2000cwo.googlegroups.com...
> 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
> 


0
Bernie
12/8/2005 7:29:23 PM
thanks so much Bernie - that's definitely what i needed and it works
wonderfully! thanks again!, rebekah

0
12/8/2005 7:42:56 PM
Reply:

Similar Artilces:

Summing one column based on date in another column
I desperately need help here. I have a spreadsheet that has sales price in one column and the date in another column. It will only add if the date is the month only i.e., January vs January 1, 2005. I need it to add by month based on all days within the month because that column has to have the actual date not just the month. For this example I'll assume date is in column A and amount is in column B: If you're just interested in a specific month (january) you could use this: =SUMPRODUCT(B1:B100,--(MONTH(A1:A100)=1)) Or, if you want to easily be able to change the month being...

Empty cells , sum of it from weekday's
Hello, i have a weekly table monday-sunday (B, C, D and so on) for each week of the year, persons are in A2, A3 A4 and so on, if a person is free on one day of the week the cell is empty, now i like to have a new table wich counts all the empty cells of a person on monday, all of them on wednesday and so on, so that i can see over a whole year, how many times someone is free on monday and so on. please can anyone help me ?, best regards, Gerard You have nicely described one week's data: name of first person in A2; and a x (or other entry) in B2 if not free Monday, in C2 if not free...

Automate users creation.
Is there a way to automate users creation using .net? Thanks, Landon bump Landon wrote: > Is there a way to automate users creation using .net? > > Thanks, Landon ...

Missing Results in Advanced Find: Calendar
When searching for appointments using advanced find, not all results appear. I'm using Outlook 2002. Is there a fix for this that Office Update does not provide? Thanks for all help!! what items are missing? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ V...

Multilanguage User Interface
Hi all We've a crm customer with branches in different languages in the same country - german/french. According Microsoft the crm multilanguage user interface will be read for version 3.0 - does somebody know if there is some possibilty or an ISV Solution available sooner? thks a lot! I'm also looking for a CRM 1.2 MUI...anybody knows something about at? Maybe some third party module? Jens "Josh" wrote: > Hi all > We've a crm customer with branches in different languages in the same > country - german/french. > According Microsoft the crm multilanguag...

disallow outbound smtp for set of users
Hi, Does any one how to resolve this question in an exchange 2003 org? 1. on a excahnge 2003 org you have a group of users that need to b able to send internal mail and external (internet) mail. 2. on the same exchange 2003 org you have a group of users that yo only want to allow to send messages internal. By no means allo messages to the internet. I was told the solution to stop the outbound mail to the internet wa to create a receipent policy with a smtp address of non-interne registered / non-resolvable name (something like testthis.com). I kne ahead of time this wasnt the solution ...

Exchange 2003 : User rights issue.
Dear all, We just found out that other user can view other user's folder without the need to login to the other user's logon. In other words, User A can view User B folder from Outlook without the need to login to User B account. This seems a security issue. I notice that under the "Mailbox Rights" for all the users, "Everyone" have "Read permission". How do disallow this without going to each of the user's mailbox and change the rights ? Is the "Mailbox Rights" (under the Exchange Advanced tab) having the same user rights as the &quo...

some users cant receive mail from certain domains
I have server03 sp1 and exchange03 sp2 and everything seemed to be working well until it came to my attention that some mail was not being received by some users. What's really strange is that in all cases my account (or any new account I create) can receive mail from the domains in question. Only existing user accounts are having problems. Mine is the admin account but I have tryed elevating existing users to admin and it makes no difference. Policies are the same for everyone. All users can send mail to the domains in question but only I receive a reply. Relpys eventually get an "u...

CPU/Ram Usage by mailbox/user/connection
There MUST be a way in Exchange to get the following information: How can I tell what mailbox/user/connection is generating the load on our mail server? Thanks. Rob- I can't say that I've ever seen such a perf counter. -- --Brian Desmond Windows Server MVP desmondb@payton.cps.k12.il.us www.briandesmond.com "Rob" <Rob@discussions.microsoft.com> wrote in message news:0FA292AE-371F-41E8-8CD9-89502173347A@microsoft.com... > There MUST be a way in Exchange to get the following information: > > How can I tell what mailbox/user/connection is generating the...

Running as a SPANISH user acts as thought the Control key is alwys down.
Our MFC-based software is such that a user can set it's preferred language to one of a number of Languages, including Spanish. When Spanish user logs into our system, it means nothing else than to perform all the string lookups in another table, besides english. Our strings don't come from Resource files, but rather from a Database table. Other than that - everything is English - the OS, the keyboard, etc - it's just that we're displaying spanish text and characters. Here's the problem - When typing into an MFC-based edit box, the keys behave as though the Control Key is ...

How to find number of emails sent by a user in a day?
Howdy, I would like to create run some statistics on the usage patterns. Is there a way to compute the number of emails send by each user in a day? We are running Exchange Server 2003 SP2. Thank you in advance for your help. Pradeep You'd need 3rd party reporting tools like Prodomag, Quest MessageStats, et al. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Pradeep" <pradeep@tapadiya.net> wrote in message news:1136329662.378227.28230@g43g2000cwa.googlegroups.com... > > Howdy, > > I woul...

Outlook Web Access users
Anyone know of any tools that will allow exporting Contacts out of Outlook Web access ? We have 1200 users who have only Web Access as their E-mail client. The only way remote users can export Exchange contacts would be via Activesync or something of that nature that I know about. Does anyone know of any other method or any third party tools that would allow that capability ? Any help in this matter would be most appreciated. Vinit Kohli Director of MIS Sibcy Cline Inc. ...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

Disable OWA By User
Without getting into a discussion of Certifictates, In A Server2003/EXCH2003 can you diable OWA by user? -- "Hell, I would Piss on a Spark Plug if it would make the Darn Thing Work" Aparently, but I have never tried it. Open the user account in Active Directory Users and Computer (exchange integrated version of course) and select the Exchange Features tab. There is an option to enable or disable Outlook Web Access there. Cheers Adam "James Stoddard" wrote: > Without getting into a discussion of Certifictates, In A Server2003/EXCH2003 > can you diable O...

Please help for getting current user name
Hi How can i get the current login user name through javascript? Please help me advance thanksfor help sujith Hi Sujith, I recently published an article about that. Look at http://www.stunnware.com/crm2/topic.aspx?id=JS8. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup ---------------------------------------------------------- "sujith" <sujithonweb@gmail.com> schrieb im Newsbeitrag news:817B67E5-76F4-44BB-AF10-F74630A5AA...

combobox after change but user hits cancel to message box
the code below is used to save some data when a user changes months on a combobox. The code prompts the user to see if he/she wants to save the estimates with a Yes, No, Cancel. yes and no cases work as expected but when the user clicks cancel, the code exits out as expected but the combox box value is now set to the new month the user selected. I would like it to be set back to the month they had in the combox before the changed it. how can I do modify the code to do that or is there a better way to do this? thanks Private Sub cboMonth_Change() Dim myCheck As Integ...

Sum Function (was summing 13 cells to the left)
Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense TIA Neil S. Hi see my reply to your old post -- Regards Frank Kabel Frankfurt, Germany Neil S. wrote: > Trying to compose a formula that will only sum 13 cells to the left > (13 months of data). When columns are added, I still only want to > sum thirteens months of data. Can this be done without constanty > changing my formulas? Make sense? ...

How can I create a folder under user's Inbox for all domain users
Dear all, Is there any approach to create a folder under Outlook 2k's Inbox for all my domain users in Exchange server-end (Exchange 2k3)? or any tools/scripts to do this? And how can I set a Server-end rules for all the uses to deliver some specific email to this folder? Thans for your inputs. Redd ...

Sum data under special condition
Hi, I have a main and detail subform. In the subform I had a Check field (Yes/No field) from the under laying table. I am having a problem of putting a sum(amount) on the main form for only item that has a check in the Yes/No filed. Could someone advice? I have Access 2003 on XP box. SF A Yes/No field has a numeric value of zero when it is "No", and -1 when it is "Yes". So, to achieve what you want, simply multiply the amount by the Yes/No field, and by -1 e.g. Sum(amount * yesnofield * -1) "SF" <xyz@online.com.kh> wrote in message news:u9...

Format changes when another user opens presentation
why do fonts change size when another user opens a shared presentation. Font size overflows the text box, the text is either too big or too small and unwraps so it goes off the slide, but when I open on my system, it's fine!? On 12/15/09 9:15 AM, in article 79A48B55-8B9D-4F5D-A21B-EF1C84D07AD2@microsoft.com, "Shevvie" <Shevvie@discussions.microsoft.com> wrote: > why do fonts change size when another user opens a shared presentation. Font > size overflows the text box, the text is either too big or too small and > unwraps so it goes off the slide, but...

Can I delete messages from users that not logged on for x months ?
On Exch2003, if I have users that never logged on for the last 6 months, can I make Exchange delete old messages from the respective mailbox automatically ? Not without some very creative scripting. Marlon Brown wrote: > On Exch2003, if I have users that never logged on for the last 6 months, can > I make Exchange delete old messages from the respective mailbox > automatically ? Run the Mailbox cleanup agent "Marlon Brown" <marlon_brown@hotmail.com> wrote in message news:uoPYkvDIFHA.2700@TK2MSFTNGP09.phx.gbl... > On Exch2003, if I have users that never logg...

sum in excel
I am trying to do a summary of hundreds of items which names start with "ENG" (there are more categories), what I would like to have is a summary of clumn E for items in column A that start with "ENG". Thanks Look in the help index for SUMIF and incorporate a wildcard *eng or use =sumproduct((left(a2:a22,3)="eng")*e2:e22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "fernango" <fernango@hotmail.com> wrote in message news:f0494714-97a6-43dc-b97b-fa72f823a3cd@b2g2000yqi.googlegroups.com... >I am ...

How do I sum a formula and paste it in 7 cell increments?
I have two worksheets I'm working with. One of them is a running total of cash for each day of the month. The second worksheet shows only the weekly totals which come from the sum of 7 days from the first worksheet. I want to know if its possible to copy the first week sum(A1:A7) and use some type of formula to then copy the formula down in 7 day increments and not have to do this manually. So the second entry would be sum(A8:A15), then sum(A16:A23), etc. Is this possible? Stephen One way: In D2: =SUM(OFFSET($A$1,(ROW()-ROW($D$2))*7,,7)) Copy down with the fill handle (the l...

Help with this macro script on single results.
Hi I put a request out a few days ago and I am attaching the macro script as well so you can see where I am up to. The problem is, if there is only one result returned for a particular staff member, the script falls with a variable object error. If there is more than 1 record, the script works fine. It just falls where a single record is returned. I am attaching the original message I sent as well as the script. PLease note that Mike, the guy that helped me immensley witht his, has also doen a few other things like auto summing which you will see in the script but not ...

How To Scan that User Clicked End Process from an application
Hey Friends Is there a way to get that User Pressed End Process from the Task Manager as VC++ do during debug mode. ...