Macro help

[Emmily]

Hi i have work task which i'm hoping can be automated. The task is to track clients who are on Payment arrangements according to their payment frequency. Every Tuesday I will manually try and work out which clients i need to chase up for payment based on their frequency. I was hoping there is VBA procedure that can be wrtitten so when i run the code it will filter for those specific clients.

Below is sample data

Excel Workbook
	A	B	C	D	E	F	G	H
1	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency
2	Mr	Jamie	Smith	Test1	7,846.64	100.00	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50.00	06/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500.00	08/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800.00	200.00	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440.00	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100.00	04/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910.00	07/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330.00	08/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700.00	08/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100.00	22/03/2011	Fortnightly
Sheet1

 

[Emmily]

I agree with your last payment due date.

My first question is, do you agree with the last payment due date, based on the start date and payment schedule?

If so, we can move to the "what generates an alert" formula.

 

[shg]

OK, so given the review date and the last payment due date, what should trigger a review, in quantitative terms?

And Emmily with two m's, I'm off to bed with my mate, and will look in on this tomorrow.

 

[Emmily]

Mate, to trigger a review is payment start date or Payment due date and the frequency.

So those clients which are weekly i would want to see True always.

Those which are monthly I would want to see True when the Current date is 7 or even 8 days later from the payment start date, so for 22/03/11 i want to see TRUE on the current Date 29/03/11

Remember a check will always happen the following week from its payment start or due date this procedure is usually with all companies, i have to allow them to make the payment, then a week later the formula will tell me which clients i need to check based on the payment frequencies.

OK, so given the review date and the last payment due date, what should trigger a review, in quantitative terms?

And Emmily with two m's, I'm off to bed with my mate, and will look in on this tomorrow.

 

[Emmily]

If was to continue to perform this task manually then on these current dates i would filter for these clients

Excel Workbook
	A	B	C	D	E	F	G	H	I
3								Current Date	15-Mar-11
4	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Check
5	Mr	Jamie	Smith	Test1	7846.64	100	29-Apr-09	Weekly	TRUE
6	Mr	Christopher	Bloggs	Test2	5830	50	6-Apr-09	Weekly	TRUE
7	Mrs	Brie	Masters	Test3	28633.44	500	8-Sep-10	Monthly	TRUE
8	Mr	Peter	Pesh	Test6	17811	100	4-Mar-11	Weekly	TRUE
9	Mr	Grant	Denyer	Test8	3960	330	8-Mar-11	Weekly	TRUE
10	Mr	Steven	Gerrard	Test9	8400	700	8-Mar-11	Monthly	TRUE
11
12								Current Date	22-Mar-11
13	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Check
14	Mr	Jamie	Smith	Test1	7846.64	100	29-Apr-09	Weekly	TRUE
15	Mr	Christopher	Bloggs	Test2	5830	50	6-Apr-09	Weekly	TRUE
16	Mr	Peter	Pesh	Test6	17811	100	4-Mar-11	Weekly	TRUE
17	Mr	Grant	Denyer	Test8	3960	330	8-Mar-11	Weekly	TRUE
18
19								Current Date	29-Mar-11
20	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Check
21	Mr	Jamie	Smith	Test1	7846.64	100	29-Apr-09	Weekly	TRUE
22	Mr	Christopher	Bloggs	Test2	5830	50	6-Apr-09	Weekly	TRUE
23	Mrs	Ben	Buckingham	Test4	800	200	22-Mar-11	Monthly	TRUE
24	Mr	Damien	Cot	Test5	4400	440	22-Mar-11	Monthly	TRUE
25	Mr	Peter	Pesh	Test6	17811	100	4-Mar-11	Weekly	TRUE
26	Mr	Grant	Denyer	Test8	3960	330	8-Mar-11	Weekly	TRUE
27	Mr	Guy	Seb	Test10	4020	100	22-Mar-11	Fortnightly	TRUE
28
29								Current Date	12-Apr-11
30	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Check
31	Mr	Jamie	Smith	Test1	7846.64	100	29-Apr-09	Weekly	TRUE
32	Mr	Christopher	Bloggs	Test2	5830	50	6-Apr-09	Weekly	TRUE
33	Mrs	Brie	Masters	Test3	28633.44	500	8-Sep-10	Monthly	TRUE
34	Mr	Peter	Pesh	Test6	17811	100	4-Mar-11	Weekly	TRUE
35	Mr	Grant	Denyer	Test8	3960	330	8-Mar-11	Weekly	TRUE
36	Mr	Steven	Gerrard	Test9	8400	700	8-Mar-11	Monthly	TRUE
37	Mr	Guy	Seb	Test10	4020	100	22-Mar-11	Fortnightly	TRUE
Sheet3

 

[jasonb75]

Based on the solution that shg has given you in #25, how do the reults compare if you :-

- Enter the check date as monday, not tuesday.

- use the formula =IF(I4<>"", OR(H4="Weekly",I$2-I4 < 7))

It seems to simple to work, but anything is possible.

 

[Emmily]

Nope that has not worked, i should not be getting TRUE for the below red cells, if the current date is 22/03/11 then i would expect TRUE for the payment dates which have 22/03/11 on 29/03/11, because on 29/03/11 I will be checking if they have payed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2								Current Date	22/03/2011
3	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Last Pmt Due	Check
4	Mr	Jamie	Smith	Test1	7,846.64	100	29/04/2009	Weekly	16-Mar-11	TRUE
5	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly	21-Mar-11	TRUE
6	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly	8-Mar-11	FALSE
7	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly	22-Mar-11	TRUE
8	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly	22-Mar-11	TRUE
9	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly	18-Mar-11	TRUE
10	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly		FALSE
11	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly	22-Mar-11	TRUE
12	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly	8-Mar-11	FALSE
13	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly	22-Mar-11	TRUE
Sheet2

Based on the solution that shg has given you in #25, how do the reults compare if you :-

- Enter the check date as monday, not tuesday.

- use the formula =IF(I4<>"", OR(H4="Weekly",I$2-I4 < 7))

It seems to simple to work, but anything is possible.

 

[jasonb75]

Based on the solution that shg has given you in #25, how do the reults compare if you :-

- Enter the check date as monday, not tuesday.

- use the formula =IF(I4<>"", OR(H4="Weekly",I$2-I4 < 7))

It seems to simple to work, but anything is possible.

22nd is still tuesday, 21st is monday.

 

[Emmily]

Ok i forgot to change the check date to Mondat, so If the check date is Monday then it works, but then how do i make it work for Tuesday as that is the day i will check for payments

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2								Current Date	21/03/2011
3	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Last Pmt Due	Check
4	Mr	Jamie	Smith	Test1	7,846.64	100	29/04/2009	Weekly	16-Mar-11	TRUE
5	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly	21-Mar-11	TRUE
6	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly	8-Mar-11	FALSE
7	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly		FALSE
8	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly		FALSE
9	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly	18-Mar-11	TRUE
10	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly		FALSE
11	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly	15-Mar-11	TRUE
12	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly	8-Mar-11	FALSE
13	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly		FALSE
Sheet2

Based on the solution that shg has given you in #25, how do the reults compare if you :-

- Enter the check date as monday, not tuesday.

- use the formula =IF(I4<>"", OR(H4="Weekly",I$2-I4 < 7))

It seems to simple to work, but anything is possible.

 

[jasonb75]

You're checking on Tuesday, but you're checking for payments due on Monday, that's why it works when you change it.

If you desperately need to keep the date as tuesaday then you could edit the "last payment due" formula to

=IF(G4>(I$2-1), "",
CHOOSE(MATCH(H4, {"Weekly","Fortnightly","Monthly","Quarterly"}, 0),
G4 + 7 * INT((I$2 -1 - G4)/7),
G4 + 14 * INT((I$2 - 1 - G4)/14),
EDATE(G4, DATEDIF(G4, I$2 -1, "m")),
EDATE(G4, 3 * INT(DATEDIF(G4, I$2 - 1, "m")/3))
))

But it just seems easier to tweak the date at source.

 

[Emmily]

Jason, for the 29/03/11 i expect the red cells to be TRUE as i would be checking a week later for the payment of 22/03/11.

I am always checking a week later from the payment due date.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2								Current Date	29/03/2011
3	Title	First Name	Last Name	Company Name	Total	Instalment	Payment Start Date	Frequency	Last Pmt Due	Check
4	Mr	Jamie	Smith	Test1	7,846.64	100	29/04/2009	Weekly	23-Mar-11	TRUE
5	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly	28-Mar-11	TRUE
6	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly	8-Mar-11	FALSE
7	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly	22-Mar-11	FALSE
8	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly	22-Mar-11	FALSE
9	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly	25-Mar-11	TRUE
10	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly		FALSE
11	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly	22-Mar-11	TRUE
12	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly	8-Mar-11	FALSE
13	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly	22-Mar-11	FALSE
Sheet2