tomkat743
07-23-2017, 06:13 AM
I have code that deletes orders that were paid after 90 days.
Sub myMacro()
paidColumn = "B"
dueColumn = "D"
firstRow = 2
lastRow = Range(paidColumn & Rows.Count).End(xlUp).Row
r = firstRow
Do Until r > lastRow
paidValue = Range(paidColumn & r).Value
dueValue = Range(dueColumn & r).Value
If paidValue > dueValue + 90 Then
Rows(r).Delete
r = r - 1
End If
r = r + 1
Loop
End Sub
I need a much more complicated version of this that does a match and lookup on two separate sheets and returns the words over 90 or under 90 so I can know if the invoices were paid within 90 days. I don't know if I am not posting the question with enough information or if what I am asking is to complicated. I have posted to Mr. Excel twice and received no responses at all
- I am more than willing to pay someone for some help on this and sent in messages and emails to their pay for play addresses and still no response - I've been working on this for months and maybe it is just not possible but somehow in my mind I believe it is possible I just need to get to the right person.
I have copied the sheets below with column G on the payments sheet being the column to populate. I need to match column "E" Invoice number on the payment sheet to column "B" on the All Inv Data sheet then look at the date in column "D" of the payment sheet and compare it to the Date in column "D" in the All Inv Data sheet and see if it is over or under the 90 day window.
As you can see in the below example the result should be over90 or under90 returned to column G on the payment sheet. As you can see that some of the invoice numbers have text before the numbers like QA1024 I don't need to do anything with these as these are credit memos or something other than paid invoices so I can skip or return a 0 or whatever.
One issue may be that the below data set contains random payments and random lines from all inv data whereas my actual sheet of payments contains about 500 invoices and my all inv data sheet has about 10,000 invoices so the below data set may have no matching invoice numbers (could this be what is making it complicated to help me) If so is there another way for me to upload both full sheets?
Is the failure on me that I am not explaining the process well enough or not supplying enough data or is this just very complicated? Or is there some other reason I may not be receiving any help on this issue? Please Please Please someone give me a direction or some feedback as to my issue. Thank you in advance for any support you can give me on this.
SHEET 1
PAYMENTS
Customer
Sales Rep
Type
Date
Invoice#
amount
over/under
NE-UNP
SR
91491
1/18/2017
129730
$10,676.53
over 90
NE-UNP
SR
91491
1/18/2017
129731
$13,816.69
over 90
NE-UNP
SR
91491
1/18/2017
129732
$199.99
over 90
NE-UNP
SR
91491
1/18/2017
135939
$191.84
under 90
NE-UNP
SR
FIX INV
3/14/2017
129731
-$7,170.57
over 90
NE-UNP
SR
FIX INV
3/14/2017
138612
-$6,646.12
under 90
NE-UNP
SR
FIX INV
3/14/2017
OA1024
$13,816.69
0.00
NE-UNP
SR
93563
4/10/2017
129729
$46,701.17
over 90
NE-UNP
SR
93563
4/10/2017
129730
$6,048.24
over 90
NE-UNP
SR
93563
4/10/2017
129733
$10,953.76
over 90
NE-UNP
SR
93563
4/10/2017
OA1057
$50.59
0.00
NE-UNP
SR
APPLY CM
5/12/2017
129732
$22,289.38
over 90
NE-UNP
SR
APPLY CM
5/12/2017
129938
$13,025.94
over 90
NE-UNP
SR
APPLY CM
5/12/2017
129939
$858.35
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130141
$241.39
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130289
$155.39
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130290
$18,716.70
over 90
NE-UNP
SR
APPLY CM
5/12/2017
135769
$913.50
over 90
NE-UNP
SR
APPLY CM
5/12/2017
139798
-$645.32
under 90
NE-UNP
SR
APPLY CM
5/12/2017
139804
-$41,688.05
under 90
NE-UNP
SR
APPLY CM
5/12/2017
OA1024
-$13,816.69
0.00
NE-UNP
SR
APPLY CM
5/12/2017
OA1057
-$50.59
0.00
SHEET 2
All INV DATA
CUSTOMER #
INVOICE
DOCUMENT
DATE
NE-UNP
129729
122408
12/17/2015
NE-UNP
129730
122414
12/17/2015
NE-UNP
129731
122415
12/17/2015
NE-UNP
129732
122416
12/17/2015
NE-UNP
129733
122413
12/17/2015
NE-UNP
129938
122408
12/28/2015
NE-UNP
129939
122414
12/28/2015
NE-UNP
129981
122408
12/29/2015
NE-UNP
130140
122418
12/31/2015
NE-UNP
130141
122415
12/31/2015
NE-UNP
130288
122408
1/12/2016
NE-UNP
130289
122415
1/12/2016
NE-UNP
130290
122416
1/12/2016
NE-UNP
130389
122414
1/15/2016
NE-UNP
130782
122408
2/9/2016
NE-UNP
131517
122414
3/23/2016
NE-UNP
131518
122408
3/23/2016
NE-UNP
132279
122415
5/2/2016
NE-UNP
135767
122414
10/25/2016
NE-UNP
135768
122415
10/25/2016
NE-UNP
135769
122416
10/25/2016
NE-UNP
135939
125912
11/2/2016
NE-UNP
136134
125912
11/15/2016
NE-UNP
136590
C02299
12/2/2016
NE-UNP
136590
C02299
12/2/2016
NE-UNP
138165
125912
2/16/2017
NE-UNP
138612
C02517
3/14/2017
NE-UNP
138613
D01897
3/14/2017
NE-UNP
139190
128254
4/6/2017
NE-UNP
139735
128494
5/5/2017
NE-UNP
139798
C02652
5/9/2017
NE-UNP
139804
C02655
5/9/2017
NE-UNP
140194
127056
5/26/2017
Sub myMacro()
paidColumn = "B"
dueColumn = "D"
firstRow = 2
lastRow = Range(paidColumn & Rows.Count).End(xlUp).Row
r = firstRow
Do Until r > lastRow
paidValue = Range(paidColumn & r).Value
dueValue = Range(dueColumn & r).Value
If paidValue > dueValue + 90 Then
Rows(r).Delete
r = r - 1
End If
r = r + 1
Loop
End Sub
I need a much more complicated version of this that does a match and lookup on two separate sheets and returns the words over 90 or under 90 so I can know if the invoices were paid within 90 days. I don't know if I am not posting the question with enough information or if what I am asking is to complicated. I have posted to Mr. Excel twice and received no responses at all
- I am more than willing to pay someone for some help on this and sent in messages and emails to their pay for play addresses and still no response - I've been working on this for months and maybe it is just not possible but somehow in my mind I believe it is possible I just need to get to the right person.
I have copied the sheets below with column G on the payments sheet being the column to populate. I need to match column "E" Invoice number on the payment sheet to column "B" on the All Inv Data sheet then look at the date in column "D" of the payment sheet and compare it to the Date in column "D" in the All Inv Data sheet and see if it is over or under the 90 day window.
As you can see in the below example the result should be over90 or under90 returned to column G on the payment sheet. As you can see that some of the invoice numbers have text before the numbers like QA1024 I don't need to do anything with these as these are credit memos or something other than paid invoices so I can skip or return a 0 or whatever.
One issue may be that the below data set contains random payments and random lines from all inv data whereas my actual sheet of payments contains about 500 invoices and my all inv data sheet has about 10,000 invoices so the below data set may have no matching invoice numbers (could this be what is making it complicated to help me) If so is there another way for me to upload both full sheets?
Is the failure on me that I am not explaining the process well enough or not supplying enough data or is this just very complicated? Or is there some other reason I may not be receiving any help on this issue? Please Please Please someone give me a direction or some feedback as to my issue. Thank you in advance for any support you can give me on this.
SHEET 1
PAYMENTS
Customer
Sales Rep
Type
Date
Invoice#
amount
over/under
NE-UNP
SR
91491
1/18/2017
129730
$10,676.53
over 90
NE-UNP
SR
91491
1/18/2017
129731
$13,816.69
over 90
NE-UNP
SR
91491
1/18/2017
129732
$199.99
over 90
NE-UNP
SR
91491
1/18/2017
135939
$191.84
under 90
NE-UNP
SR
FIX INV
3/14/2017
129731
-$7,170.57
over 90
NE-UNP
SR
FIX INV
3/14/2017
138612
-$6,646.12
under 90
NE-UNP
SR
FIX INV
3/14/2017
OA1024
$13,816.69
0.00
NE-UNP
SR
93563
4/10/2017
129729
$46,701.17
over 90
NE-UNP
SR
93563
4/10/2017
129730
$6,048.24
over 90
NE-UNP
SR
93563
4/10/2017
129733
$10,953.76
over 90
NE-UNP
SR
93563
4/10/2017
OA1057
$50.59
0.00
NE-UNP
SR
APPLY CM
5/12/2017
129732
$22,289.38
over 90
NE-UNP
SR
APPLY CM
5/12/2017
129938
$13,025.94
over 90
NE-UNP
SR
APPLY CM
5/12/2017
129939
$858.35
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130141
$241.39
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130289
$155.39
over 90
NE-UNP
SR
APPLY CM
5/12/2017
130290
$18,716.70
over 90
NE-UNP
SR
APPLY CM
5/12/2017
135769
$913.50
over 90
NE-UNP
SR
APPLY CM
5/12/2017
139798
-$645.32
under 90
NE-UNP
SR
APPLY CM
5/12/2017
139804
-$41,688.05
under 90
NE-UNP
SR
APPLY CM
5/12/2017
OA1024
-$13,816.69
0.00
NE-UNP
SR
APPLY CM
5/12/2017
OA1057
-$50.59
0.00
SHEET 2
All INV DATA
CUSTOMER #
INVOICE
DOCUMENT
DATE
NE-UNP
129729
122408
12/17/2015
NE-UNP
129730
122414
12/17/2015
NE-UNP
129731
122415
12/17/2015
NE-UNP
129732
122416
12/17/2015
NE-UNP
129733
122413
12/17/2015
NE-UNP
129938
122408
12/28/2015
NE-UNP
129939
122414
12/28/2015
NE-UNP
129981
122408
12/29/2015
NE-UNP
130140
122418
12/31/2015
NE-UNP
130141
122415
12/31/2015
NE-UNP
130288
122408
1/12/2016
NE-UNP
130289
122415
1/12/2016
NE-UNP
130290
122416
1/12/2016
NE-UNP
130389
122414
1/15/2016
NE-UNP
130782
122408
2/9/2016
NE-UNP
131517
122414
3/23/2016
NE-UNP
131518
122408
3/23/2016
NE-UNP
132279
122415
5/2/2016
NE-UNP
135767
122414
10/25/2016
NE-UNP
135768
122415
10/25/2016
NE-UNP
135769
122416
10/25/2016
NE-UNP
135939
125912
11/2/2016
NE-UNP
136134
125912
11/15/2016
NE-UNP
136590
C02299
12/2/2016
NE-UNP
136590
C02299
12/2/2016
NE-UNP
138165
125912
2/16/2017
NE-UNP
138612
C02517
3/14/2017
NE-UNP
138613
D01897
3/14/2017
NE-UNP
139190
128254
4/6/2017
NE-UNP
139735
128494
5/5/2017
NE-UNP
139798
C02652
5/9/2017
NE-UNP
139804
C02655
5/9/2017
NE-UNP
140194
127056
5/26/2017