VB help please!

[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.

 

[Guest]

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 - 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 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 - 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 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

 

[wj]

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.

 

[Guest]

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