Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
Hi Everyone,
i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
With large amount of data like this I wouldn't put a formula into a worksheet
because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
Hi Joel,
Thanks for the codes, however its not working the result are value "Joel" wrote: With large amount of data like this I wouldn't put a formula into a worksheet because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
There was two problems with the code
1) I had = instead of = 2) The function sumproduct wanted a string date and not a number date. Made some minor changes. I converted the dates on the worksheet to string using a Format function. Then had to convert the string date back to a number date using DateValue. The Associate Principal in math does not apply to VBA code. UGH!!!!!!!! Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = Format(.Range("B" & RowCount), "MM/DD/YYYY") MaxDate = Format(.Range("C" & RowCount), "MM/DD/YYYY") Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MinDate & """)<=" & contractDate.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MaxDate & """)=" & contractDate.Address(external:=True) & _ ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Joel, Thanks for the codes, however its not working the result are value "Joel" wrote: With large amount of data like this I wouldn't put a formula into a worksheet because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
Just as a reminder the Associate Proincipal of math says
if A = B and B = C then A = C. In this case A didn't equal C (it should of) , but going from A to B and then B to C got the correct answer. "David" wrote: Hi Joel, Thanks for the codes, however its not working the result are value "Joel" wrote: With large amount of data like this I wouldn't put a formula into a worksheet because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
Hi Joel,
I got an error message "Argument not optional" and it highlights the first line LastRow "Joel" wrote: There was two problems with the code 1) I had = instead of = 2) The function sumproduct wanted a string date and not a number date. Made some minor changes. I converted the dates on the worksheet to string using a Format function. Then had to convert the string date back to a number date using DateValue. The Associate Principal in math does not apply to VBA code. UGH!!!!!!!! Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = Format(.Range("B" & RowCount), "MM/DD/YYYY") MaxDate = Format(.Range("C" & RowCount), "MM/DD/YYYY") Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MinDate & """)<=" & contractDate.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MaxDate & """)=" & contractDate.Address(external:=True) & _ ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Joel, Thanks for the codes, however its not working the result are value "Joel" wrote: With large amount of data like this I wouldn't put a formula into a worksheet because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to add numbers based on set criteria
the lengths of the lines were too long and the posting add additional lines
which cause errors. I slighlty modified the code to prevent this from happening. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = Format(.Range("B" & RowCount), "MM/DD/YYYY") MaxDate = Format(.Range("C" & RowCount), "MM/DD/YYYY") Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MinDate & """)<=" & _ contractDate.Address(external:=True) & ")," & _ "--(DateValue(""" & MaxDate & """)=" & _ contractDate.Address(external:=True) & _ ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Joel, I got an error message "Argument not optional" and it highlights the first line LastRow "Joel" wrote: There was two problems with the code 1) I had = instead of = 2) The function sumproduct wanted a string date and not a number date. Made some minor changes. I converted the dates on the worksheet to string using a Format function. Then had to convert the string date back to a number date using DateValue. The Associate Principal in math does not apply to VBA code. UGH!!!!!!!! Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = Format(.Range("B" & RowCount), "MM/DD/YYYY") MaxDate = Format(.Range("C" & RowCount), "MM/DD/YYYY") Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MinDate & """)<=" & contractDate.Address(external:=True) & _ ")," & _ "--(DateValue(""" & MaxDate & """)=" & contractDate.Address(external:=True) & _ ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Joel, Thanks for the codes, however its not working the result are value "Joel" wrote: With large amount of data like this I wouldn't put a formula into a worksheet because it would be very slow. Instead I would use an evaluate like below. I made sheet 1 your first table and sheet 2 the 2nd table. Sub GetTotals() With Sheets("Sheet2") LastRow = .Range("A" & Rows.Count).End(xlUp).Row Set contractNum = .Range("A2:A" & LastRow) Set contractDate = .Range("B2:B" & LastRow) Set contractAmount = .Range("C2:C" & LastRow) End With With Sheets("sheet1") RowCount = 2 Do While .Range("A" & RowCount) < "" contract = .Range("A" & RowCount) MinDate = .Range("B" & RowCount) MaxDate = .Range("C" & RowCount) Total = Evaluate("Sumproduct(" & _ "--(" & contract & "=" & contractNum.Address(external:=True) & ")," & _ "--(" & MinDate & "<=" & contractDate.Address(external:=True) & ")," & _ "--(" & MaxDate & "=" & contractDate.Address(external:=True) & ")," & _ "(" & contractAmount.Address(external:=True) & "))") .Range("D" & RowCount) = Total RowCount = RowCount + 1 Loop End With End Sub "David" wrote: Hi Everyone, i am trying to create a macro that will add numbers based on set criteria here is an example I have a file over 20K lines and about 200 columns which contain the following headings Contract # Min date Max date 55 01/01/09 05/10/09 200 10/11/06 12/22/07 350 11/14/05 01/08/06 and i havea nother sheet in the same file with records of over 70K, which keeps the payments records, and contains the following headings Contract # Posting date Amount 55 03/10/09 100.00 200 11/15/06 80.00 55 02/02/06 200.00 350 12/11/05 500.00 55 02/28/09 90.00 55 07/22/09 22.00 i want the macro to add the amount column for each contract with posting date the falls between min date and max date. I appreciate any help i can get thanks david |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro filter based on date criteria | Excel Discussion (Misc queries) | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
offset macro based on multiple criteria | Excel Discussion (Misc queries) | |||
Sum numbers based on specified criteria | Excel Worksheet Functions | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) |