J
JT
I am creating a macro to help field users match debits and credits in a form.
Before displaying the items in a form, I want to eliminate items that aren't
potential matches. The debits and credits are considered a potential match
if they are within 99 cents of each other in either direction. (I use the
absolute value of the credit to create an upper and lower range to determine
if it is within 99 cents.) As the macro goes through the list, I want to (1)
delete columns 1 - 8, (2) delete columns 10 - 17, or leave both items as a
potential match. I'm having a hard time getting this to work. Here is a
sample of the data:
DR CR lower Upper
' Note / Comments
Row 1 0.73 1.05 0.06 2.04 '
both items stay
Row 2 22.62 1.72 0.73 2.71 '
matches row 1
Row 3 25.00 1.72 0.73 2.71 '
matches row 1
Row 4 30.00 1.84 0.85 2.83 '
no match - delete CR
Row 5 30.00 23.99 23.00 24.98 ' no
match-delete22.62
I need to consider the debits and credits separately. I was considering
looking at the first debit and moving through the credits until I get to a
lower that is greater than the dr I'm looking at.
Then I need to go to the next DR(22.62) and consider the CR I stopped on and
see if that is a match or smaller than the lower (in which case I would
delete cols 1 - 8 row 2 )or larger than the CR (in which case I would delete
cols 10 - 17 ) the row the credit is in.
There is a lot of back and forth and I'm finding this confusing to code.
Any suggestions on how to accomplish this would be GREATLY appreciated. I'm
about ready to pull what;s left of my hair after trying to get this done for
the last 3 days.
Thanks for the help. Let me know if you need any more info......
Before displaying the items in a form, I want to eliminate items that aren't
potential matches. The debits and credits are considered a potential match
if they are within 99 cents of each other in either direction. (I use the
absolute value of the credit to create an upper and lower range to determine
if it is within 99 cents.) As the macro goes through the list, I want to (1)
delete columns 1 - 8, (2) delete columns 10 - 17, or leave both items as a
potential match. I'm having a hard time getting this to work. Here is a
sample of the data:
DR CR lower Upper
' Note / Comments
Row 1 0.73 1.05 0.06 2.04 '
both items stay
Row 2 22.62 1.72 0.73 2.71 '
matches row 1
Row 3 25.00 1.72 0.73 2.71 '
matches row 1
Row 4 30.00 1.84 0.85 2.83 '
no match - delete CR
Row 5 30.00 23.99 23.00 24.98 ' no
match-delete22.62
I need to consider the debits and credits separately. I was considering
looking at the first debit and moving through the credits until I get to a
lower that is greater than the dr I'm looking at.
Then I need to go to the next DR(22.62) and consider the CR I stopped on and
see if that is a match or smaller than the lower (in which case I would
delete cols 1 - 8 row 2 )or larger than the CR (in which case I would delete
cols 10 - 17 ) the row the credit is in.
There is a lot of back and forth and I'm finding this confusing to code.
Any suggestions on how to accomplish this would be GREATLY appreciated. I'm
about ready to pull what;s left of my hair after trying to get this done for
the last 3 days.
Thanks for the help. Let me know if you need any more info......