W
wj
Hi,
Please help this novice set up a VB macro to dump data into the right
columns.
I'm looking to setup a macro that will look up all dates in Column(X) and
then copy the Total Accounts Receivable data into corresponding AR Range
columns of 31-45 Day AR, 46-60 Day AR, 61-90 Day AR & 90 Day Beyond AR
columns base on the last day of last month (eg. last day of Oct.2003).
Here's an example:
Column A contains Account#, B contains company name, C contains PO#, D
contains invoice#, E contains Date of Invoice, F contains Accounts
Receivable Amount.
Company XXX has invoice date(E) of 9/20/03 with $100 AR Amount(F).
company ZZZ has invoice date(E) of 8/10/03 with $200 AR Amount(F).
using Oct.30 as last day of the month.
I want the macro to be able to look at the 2 dates at column E 9/20/03 &
8/10/03 and copy the data in column F to the appropriate AR range columns.
In this case, $100 goes to 31-45 Day AR & $200 goes to 46-60 Day AR
columns.
The general setup looks like this:
Column A - date
Column B - total AR
Column C - 30-45 Day AR
Column D - 46-60 Day AR
Column E - 61-90 Day AR
Column F - 90 & Beyond AR
If date in Column(A) is < 30 days from the last day of last month, then do
nothing.
If date in Column(A) is > 30 days but < 45 days from the last day of last
month, then copy the data in Column(B) to Column(C)
If date in Column(A) is > 46 days but < 60 days from the last day of last
month, then copy the data in Column(B) to Column(D)
If date in Column(A) is > 61 days but < 90 days from the last day of last
month, then copy the data in Column(B) to Column(E)
If date in Column(A) is > 90 days & beyond from the last day of last month,
then copy the data in Column(B) to Column(F)
If date in Column(A) Empty, then skip.
Any help on setting it up would be greatly appreciated. Thanks.
PS. I'm using Excel 2003.
Please help this novice set up a VB macro to dump data into the right
columns.
I'm looking to setup a macro that will look up all dates in Column(X) and
then copy the Total Accounts Receivable data into corresponding AR Range
columns of 31-45 Day AR, 46-60 Day AR, 61-90 Day AR & 90 Day Beyond AR
columns base on the last day of last month (eg. last day of Oct.2003).
Here's an example:
Column A contains Account#, B contains company name, C contains PO#, D
contains invoice#, E contains Date of Invoice, F contains Accounts
Receivable Amount.
Company XXX has invoice date(E) of 9/20/03 with $100 AR Amount(F).
company ZZZ has invoice date(E) of 8/10/03 with $200 AR Amount(F).
using Oct.30 as last day of the month.
I want the macro to be able to look at the 2 dates at column E 9/20/03 &
8/10/03 and copy the data in column F to the appropriate AR range columns.
In this case, $100 goes to 31-45 Day AR & $200 goes to 46-60 Day AR
columns.
The general setup looks like this:
Column A - date
Column B - total AR
Column C - 30-45 Day AR
Column D - 46-60 Day AR
Column E - 61-90 Day AR
Column F - 90 & Beyond AR
If date in Column(A) is < 30 days from the last day of last month, then do
nothing.
If date in Column(A) is > 30 days but < 45 days from the last day of last
month, then copy the data in Column(B) to Column(C)
If date in Column(A) is > 46 days but < 60 days from the last day of last
month, then copy the data in Column(B) to Column(D)
If date in Column(A) is > 61 days but < 90 days from the last day of last
month, then copy the data in Column(B) to Column(E)
If date in Column(A) is > 90 days & beyond from the last day of last month,
then copy the data in Column(B) to Column(F)
If date in Column(A) Empty, then skip.
Any help on setting it up would be greatly appreciated. Thanks.
PS. I'm using Excel 2003.