loop VBA request - 109082

I post below earlier on but I couldnt see the thread, if duplicated please 
exscute me.

I have 3 columns contain names(unknown, may duplicated and dynamic row), 
date and amount; I am looking a way to get a sum and count on data that:
1) date is equal to or greater than today, then
2) loop thru name and subtotal on each customer
3) sum subtotal if value is equal or above 10k
3) count 2) with same name treat as once

4/25/2010 7:23:01 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 55

Your post is not entirely clear.. notice that you have had no responses.
If the customers are not sorted in order, then you will have to loop through 
the data once for each customer that fits the criteria.

Since you want totals, I would find a customer, sum them up using your 
criteria, and post the customer name and totals to A DIFFERENT worksheet.  I 
have written similar applications where the solution is just posted to 
another worksheet.

For example, you find a date that meets your criteria.  Then find every 
occurrence of that customer, do the math, then post the totals to another 
worksheet, keeping track of the last row posted in the other worksheet.  Keep 
looping, posting each customer total to the next available row in the other 

The programming for this could be somewhat involved, but not too difficult.

Rich Locus, Logicwurks, LLC
Rich Locus
Logicwurks, LLC

"Seeker" wrote:

> I post below earlier on but I couldnt see the thread, if duplicated please 
> exscute me.
> I have 3 columns contain names(unknown, may duplicated and dynamic row), 
> date and amount; I am looking a way to get a sum and count on data that:
> 1) date is equal to or greater than today, then
> 2) loop thru name and subtotal on each customer
> 3) sum subtotal if value is equal or above 10k
> 3) count 2) with same name treat as once
> Tks
4/25/2010 5:43:01 PM

Similar Artilces:

vba error when referencing the FullName of a contact
I am getting a number of what appear to strange errors. The following code plus some debug MsgBox statements iare erroring out and I am looking for some suggestion as to why this might be Error indicated below at the ******<- code stops with an runtime error: Method FullName of ContactItem failed *****. Code from VBA help Sub UseEntryID() Dim myOlApp As Outlook.Application Dim myNamespace As Outlook.NameSpace Dim myContacts As Outlook.MAPIFolder Dim myItem1 As Object Dim myItem2 As Object Set myOlApp = CreateObject("Outlook.Application") Set myNa...

Forcing Combo box entry in VBA
In my user form, I have several combo boxes that take their source from various named ranges. The drop down lists work fine but at the moment, a user is able to overwrite whats in the dropdown. How can I ensure that only what is in the drop down is chosen? TIA "Anthony Slater" <AnthonySlater@discussions.microsoft.com> wrote in message news:7D124AC9-8614-4619-B8B9-8D1C7AE5A4FB@microsoft.com... > In my user form, I have several combo boxes that take their source from > various named ranges. > > The drop down lists work fine but at the moment, a user is able to o...

Chart Title entering text VBA
Hi, Hope someone can help; At work we import a large number of textfiles which when imported int excel are graphed. We get the titles from the textfiles and this i fine for all but one file. What i want to do is edit the chart title t add text after it has imported. However the first part is variable. So in summary i want the chart title as it is, but then to add tex afterwards to the existing title. Is this possible? so "Graph ????" is the title but i want to make it "Graph ????: tes text" The below obviously just completely changes the title. Sheets("Asthma Gr...

Inputbox in VBA
Hello, I have the following VBA macro: " If WorksheetFunction.CountIf(Range("E394:E709"), Range("E900")) <> 1 Then Range("E" & i).EntireRow.Delete Else Range("L" & i).Value = ans End If End If Next i" I would like to know if it is possible to have an inputbox in VBA that would give me the option to the change this range "(Range("E394:E709"). Jeff Jeff, Something along the lines of Set oRng = Range("E394:E709"), On Error Resume Next Set oRng2 = In putbox("Select a range or cancel...

Excel Export to Access via VBA
Hi all. Hopefully someone can point me in the right direction. Here's what I want to do. I want to be able to click on a command button and export whatever logs that have been entered into the spreadsheet to be exported into an Access db with the same field names as the spreadsheet. I've read several postings on here but can't seem to get the coding down. I keep getting an error ont he first line "user-defined type not defined". Below is the code I got from a different posting here. Any help is appreciated. Thanks. Dim con As New adodb.Connection ...

Reference sub-subform though VBA
I need to reference controls and properties on a subform that is two levels down from the main form. VBA is on main form is: Me.SubFormControl.Form.SubFormControl.Form.[control or property] I get an "Application-defined or object-defined error" message. Any suggestions? That should work. Double check the names of the subform controls. They can be different from the name of the form they contain. You could try a bang instead of a dot: Me!SubFormControl.Form!SubFormControl.Form![control] If it still fails, open the Immediate Window (Ctrl+G), and take it a step at a time...

The following statement produces an error that DoCmd.RunSQL requires an argument that is an SQL statement: DoCmd.RunSQL "SELECT * & _ "FROM _SCHOOLS" _SCHOOL is the name of my Access table. Why is this not a valid SQL statement? Thank you for your advice. Sincerely, Karen Boyer Xetex Business Systems 610-898-1551 1-800-356-2772 Ext 12 "Karen Boyer" <kb@xebra.com> wrote in message news:OhFg$UT%23HHA.3548@TK2MSFTNGP06.phx.gbl... > The following statement produces an error that DoCmd.RunSQL requires an > argument that is an SQL statement...

setfocus() endless loop
I've got two dialogs, one embedded within the other. I'd like to set the focus on a Edit-control of the embedded dialog, by calling setFocus() in onInitDialog() (return FALSE) of the embedded dialog. This works fine so far, as long as the edit fields of both dialogs are activ. However, when the edit controls are set inactiv, the application gets stuck and the cursor is blinking in a control of the outer dialog, although it is never set there in the code. If I remove the desired setFocus(), everything works fine, also with inactiv controls. Probably, the setFocus() on the desired ...

Using User input in excel VBA
I'm supposed to make an invoice of 4 items. Each item needs to have user input for the description, price, and quantity ordered. Then I need to calculate the cost of the 4 items. Then calculate the sales tax at 6%. Then calculate the total cost including sales tax. This needs to be done using VBA code in excel. --I'm curious as to how to prompt the user to give me information. --I'm curious as to how I would use the users input in another cell once they entered it. -- I'm curious also as to how VB will know that something is entered once the user enters something into t...

Working with non-default folders in VBA
I am trying to use VBA to write out a file, containing names and telephone numbers contained in a non-default folder. It is a Contacts folder, in my "Personal Folder" collection, called Hennepin, which exists as a subfolder of the default Contacts folder. The routine attempts to write out all names and business telephone numbers to a file called HennepinFile.txt. If and when I can get this simple operation to work, I hope to do some more complicated things with it. Unfortunately, It does not yet work. I am an end user, not a programmer, and do not yet understand this very well. ...

MAC Compatibility for VBA!
Hi folks, Any Windows/Mac experts to advise me if an Excel worksheet with VB programming built into it is compatible at all with Excel for MA computers? Mac computers seem to open the worksheet but not be able t read the VBA coding. Is there a way to achieve this compatibility? Thanks. x -- Message posted from http://www.ExcelForum.com MacXL uses VBA5, so if your code works in XL97, it will likely work in MacXL, with a couple of exceptions: - MacXL does not support ActiveX, including ActiveX controls from the Controls Toolbox - use Forms controls instead - There are a (very) few sy...

salut j'ai une probleme sur vba que je n'arrive pas resoudre : j'ai un tableau de donnees (qui sont rentrees avec des userform). pour supprimer une ligne de ce tableau, j'ai cree un userform avec une liste deroulante dont la rowsource est la premiere colonne de mon tableau. je voudrais , en selectionnant une valeur ds cette liste, qu'elle soit selectionnee ds mon classeur excel pour que je puisse supprimer la ligne. merci de votre aide -- Ce message a ete poste via la plateforme Web club-Internet.fr This message has been posted by the Web platform club-Internet.fr ...

Shedule task keeps saying The resource requested does not meet the
I have created a service but when trying to schedule the task it keep giving this Red X on top saying "The resource requested does not meet the minimum requirements of the service resource" Please is there something I am missing? ...

set calendar coloring filters through VBA
I need to set de calendar coloring filters through vba. For outlook versions 2003 and later. I know this can be set manual, but i need it to be done by programming. I would like VBA for this if its not possible vbscript if not possible vb.net Automatic formatting is not exposed in the object model for Outlook 2003. It doesn't matter what language you plan to use. In Outlook 2007 you can use the CalendarView.AutoFormatRules collection to access the auto-formatting. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Professional Programming Outlook 2007. Reminder...

Wont copy and paste using VBA
I have a very large worksheet with a number of people on it. There are multiple rows of data for each of these people. I have managed to write code to set up a member tab for each of these people (over 1000 !) but what I now want to do is copy the data for each person to the relevant worksheet. This is my code: Sub ExportData() Dim wb As Workbook Dim ws As Worksheet Dim strSheetName As String Set wb = Workbooks("PWSallocationsv1 3 (2).xls") For Each ws In wb.Worksheets If Not ws.Name = "Sheet1" Then If Not ws.Name = "Raw Data" Then 'M...

Find and Copy loop problem
I am trying to create a macro that defines a range, and if it finds a cell with a "Y" in it copies and moves the cells next to it. Unfortunately, it doesn't seem to do any finding. I just copies whatever it is next to. I'm probably not defining the loop correctly. Any help? Dim RngToSearch As Range Dim RngToFind As Range Dim RngCopy As Range Sheets("Bid Generation").Select Set RngToSearch = Range("G9:G1003") Set RngToFind = RngToSearch.Find("Y") If RngToFind Is Nothing Then Else For Each cell In RngToFind ActiveCell.Offset(0...

In VBA how do I link the fields of a form to an on-the-fly SQL que
Hi. I have a form whose fields are linked to an underlying table of constellations and their variable stars. When the form opens it is initially linked to the entire table-- all the stars in all the constellations. On the form, I create a SQL query by searching on a single constellation, and I want to be able to do this over and over again as I work through the various constellations. I don't want to save the query-- just a temporary one in memory while I am adding new/updating old records for the selected stars. I can create the query OK-- the SQL works fine. But once a new qu...

Bug report for VBA on Office 2004 for Mac
As I don't want to use MS Passport, I hope that this will suffice as a bug report for Office 2004 for the Mac. I hope that someone will pass this along to the proper person. Anyway, if you have even one Cyrillic character (or probably one from any other non-Mac Roman script) in your hard drive volume name, VBA will not launch from within Word. I haven't tested the same from within Excel or other apps, or launching VBA by itself. This applies to the creation and recording of macros as well as the Visual Basic Editor itself. Here's hoping that someone reports this and that MS fi...

Filter SendObject Request
Below is the code that I have associated with a couple of buttons on a form that I am using. The button in question is the Form_Email_Send_Report_PackInfo at the bottom. This is what I would like to do. Similar to the print procedure (Button_Report_Print_PackInfo), I would like to filter the report that I open to the current record that is being viewed. I don't need the extra records. Just the one. The help on the coding does not show a [Where] section in the coding line. But is there a way to add one where I am only sending the current record similar to printing only the cu...

Outlook 97 will not receive Meeting requests from Outlook 2003 users
Outlook 97 will not receive Meeting requests from Outlook 2003 users. Is there a fix for 97 users so they can receive 2003 meeting requests? You should try not including a reminder in the meeting requests. If that doesn't work, tell us about your mail environment -- are you using Exchange? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "matt" <anonymous@discussions.microsoft.com> wrote in message news:3a4401c47f00$d80acad0$a601280...

SendForReview VBA code doesn't work when I changed to PP 2007
I'm a teacher who has created a program of quizzes and exams for students in many facilities. I used the SendForReview in PP 2003 and it worked fine. Now, with an upgrade to 2007, I get a message saying "Invalid request. This method or procedure is no longer supported by this version of PowerPoint." I'm not a programmer--is there an easier way around this problem? (I tried saving the version in the compatability mode / earlier versions--but no luck....) Thanks Office Developer Reference MsoEnvelope.Item Property Gets a MailItem object that can be us...

HTTP 400 Bad Request Error with Outlook Web Access & Vista
I have Windows Vista Home Premium on my desktop PC. I had previously been able to access my work email via Outlook Web Access 2003. Starting last Tuesday, 10/16, when I go to log in, I immediately get an HTTP 400 Bad Request Error, Webpage Cannot Be Found response. Another PC running Windows XP SP2, works fine. Both machines run IE 7. Hello Salty, You've landed in a discussion group for users of Mac versions of Office (not your fault: the interface is badly designed). Although there are several people here who are experts in both Mac and PC and they may come by soon, you're ...

Request help regarding MSChart control with VC++
Hey Everyone I'm using Visual C++ under Visual Studio 6. I have very little experience with this kind of thing, so please excuse any ignorance. Here's my scenario: I have a data series of type float that is at most 800 points. The minimum and maximum bounds are variable. I also have an arbitrary number of additional data series. The size of these series is not known in advance, but can easily be found at runtime. All X values fall within the same range. Here's an example of a typical data set: Data series 1 (800 or fewer points): {30000, 0.5} {30002, 0.6} {30005, ...

Problem using VBA functions....
Hello All, Function Celsius(dFahrenheit As Double) As Double Celsius = (dFahrenheit - 32) * (5 / 9) End Function As a demo, I have been trying to get a simple VBA function to work using Excel without success. I believe I've created it successfully using the Visual Basic Script Editor. I have tried using it in a worksheet, but whenever I try the formula in a cell, I keep getting #NAME?. EG. Cell A2 "=Celsius(3)" No matter what I try to do, it will not accept this formula even though I believe it's correct. From my investigation, it appears that the worksheet for wh...

the class installer has denied the request
I have an usb benq scanner s2w4300u and I installed it many times before and there was no problem at all, last time whn I connected it and was trying to install it I got failed, I always receive the message "the class installer has deied the request to install or upgrade this device" I tried many solutions such as uninstalling all USB drivers, removing all usb peripherals connected to the PC, installing new drivers for that scanner,... I hope to help me thanks, Ali El-Matarawy "Ali El-Matarwy" <AliElMatarwy@discussions.microsoft.com> wrote in m...