TinaP
Well-known Member
- Joined
- Jan 26, 2005
- Messages
- 528
Hi everyone!
I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula
All of the ranges are dynamic.
The actual sheet has more than 12,000 records. I import, create dynamic named ranges and insert the helper column in VBA. I would love to simplify the macro in order to speed it up. This is the code to add the helper column which takes the most time:
Can anyone help?
I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.
I've created a helper column where I've inserted the formula
Code:
=SUMPRODUCT(--(DateRange=A2),--(PurchaserRange=G2),--(VoidRange<>"Y"),AmtRange)
Log Test.xls | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | DATE | CK# | BR | AMT | TYPE | PAYEENAME | PURCHASERNAME | PURCHASERADDRESS | CUST | VOID | CASHIN | HELPER | ||
2 | 11/2/2006 | 25944 | 2 | 3750 | C1 | a | A | - | 3,750.00 | |||||
3 | 11/2/2006 | 142434 | 14 | 795 | M1 | b | B | N | 798.00 | 795.00 | ||||
4 | 11/2/2006 | 41727 | 4 | 398.5 | M1 | C | Y | 42.85 | 3,098.50 | |||||
5 | 11/2/2006 | 41728 | 4 | 2700 | M1 | C | Y | 42.42 | 3,098.50 | |||||
6 | 11/2/2006 | 52247 | 5 | 303.13 | C1 | e | D | Y | - | 303.13 | ||||
7 | 11/2/2006 | 143344 | 14 | 5013.24 | C1 | f | E | - | 5,013.24 | |||||
8 | 11/2/2006 | 1027377 | 1 | 308592.15 | C1 | g | F | - | 308,592.15 | |||||
9 | 11/2/2006 | 1027378 | 1 | 682 | C1 | h | G | - | 682.00 | |||||
10 | 11/2/2006 | 1027379 | 1 | 1350 | C1 | i | H | Y | - | 1,350.00 | ||||
11 | 11/2/2006 | 143345 | 14 | 68 | C1 | j | I | - | 68.00 | |||||
12 | 11/3/2006 | 121565 | 12 | 4600 | C1 | j | J | 4,600.00 | 4,600.00 | |||||
Sheet1 |
The actual sheet has more than 12,000 records. I import, create dynamic named ranges and insert the helper column in VBA. I would love to simplify the macro in order to speed it up. This is the code to add the helper column which takes the most time:
Code:
Sub Add_Helper()
Dim Cell As Range
Application.Calculation = xlManual
Application.ScreenUpdating = False
For Each Cell In Range("DateRange")
Cell.Offset(0, 11) = "=SUMPRODUCT(--(DateRange=" & Cell.Address & "),--(PurchaserRange=" & Cell.Offset(0, 5).Address & "),--(VoidRange<>""Y""),AmtRange)"
Next Cell
Calculate
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Can anyone help?