Thanks! This works great. Just 1 more question though, is there a way not
to copy negative #s in the Total AR(column B) to the XX Day AR columns? I
only want the positive # to be included. Thanks.

?B?RWR3aW4gVGFtIChNUyBNVlAp?= wrote in
:

Of course, there are ways to solve your problem using custom formula
built with VBA. However, it's easier to solve your porblem using
Excel's built-in worksheet functions.

For example, you got:
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

In cell C2, type the formula:
=IF(AND(NOW()-$A2=30,NOW()-$A2<=45),$B2,"")
In cell D2, type the formula:
=IF(AND(NOW()-$A2=46,NOW()-$A2<=60),$B2,"")
In cell E2, type the formula:
=IF(AND(NOW()-$A2=61,NOW()-$A2<=90),$B2,"")
In cell F2, type the formula:
=IF(NOW()-$A2=90,$B2,"")


Then you can drag the formulas downward to fill-up your table.


----- wj wrote: -----

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.

Sure. You might want to learn the ABS function

For example, in C2, modify the formula to
= ABS(IF(AND(NOW()-$A2=30,NOW()-$A2<=45),$B2,"")


----- wj wrote: ----

Thanks! This works great. Just 1 more question though, is there a way not
to copy negative #s in the Total AR(column B) to the XX Day AR columns? I
only want the positive # to be included. Thanks

?B?RWR3aW4gVGFtIChNUyBNVlAp?= wrote i
:

Of course, there are ways to solve your problem using custom formul
built with VBA. However, it's easier to solve your porblem usin
Excel's built-in worksheet functions.
For example, you got
Column A - dat
Column B - total A
Column C - 30-45 Day A
Column D - 46-60 Day A
Column E - 61-90 Day A
Column F - 90 & Beyond A
In cell C2, type the formula
=IF(AND(NOW()-$A2=30,NOW()-$A2<=45),$B2,""
In cell D2, type the formula
=IF(AND(NOW()-$A2=46,NOW()-$A2<=60),$B2,""
In cell E2, type the formula
=IF(AND(NOW()-$A2=61,NOW()-$A2<=90),$B2,""
In cell F2, type the formula
=IF(NOW()-$A2=90,$B2,""
Then you can drag the formulas downward to fill-up your table
----- wj wrote: ----
Hi
Please help this novice set up a VB macro to dump data into th
right columns.
I'm looking to setup a macro that will look up all dates i
Column(X) and then copy the Total Accounts Receivable data int
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 o
last month (eg. last day of Oct.2003).
Here's an example:
Column A contains Account#, B contains company name, C contain
PO#, D contains invoice#, E contains Date of Invoice, F contain
Accounts Receivable Amount.
Company XXX has invoice date(E) of 9/20/03 with $100 A
Amount(F). company ZZZ has invoice date(E) of 8/10/03 with $20
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
9/20/03 & 8/10/03 and copy the data in column F to th
appropriate AR range columns.
In this case, $100 goes to 31-45 Day AR & $200 goes to 46-60 Da
AR columns
The general setup looks like this
Column A - dat
Column B - total A
Column C - 30-45 Day A
Column D - 46-60 Day A
Column E - 61-90 Day A
Column F - 90 & Beyond A
If date in Column(A) is < 30 days from the last day of las
month, then do
nothing
If date in Column(A) is 30 days but < 45 days from the last da
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 da
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 da
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 o
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