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]

If i was to run the code on Tuesday 15/03/11 i would expect the code to filter for the highlights rows

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	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly
Sheet2

 

[Emmily]

Another few examples.

If i was to run the code on say 22/03/11 or even 23/03/11, i expect the macro to filter for these yellow rows

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	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly
Sheet2

If i was to run the code on say 29/03/11 or even 30th i would expect the code to filter for these yellow rows

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	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly
Sheet2

 

[Dryver14]

Emmily,

You posted the start position can you show us the desired result from that?

 

[Dryver14]

sorry I had not turned the page...
when you run the code on the 15th you 2 monthly dates highlighted with start dates of the 8th of the month...would they not have shown up the week before?

 

[Emmily]

sorry I had not turned the page...
when you run the code on the 15th you 2 monthly dates highlighted with start dates of the 8th of the month...would they not have shown up the week before?

Hi Dryver, the reason why those 2 dates would not have shown before is because the 8th is the payment date and payment would take at least an extra day to come through so the code can never filter for that same day, it will always be week after.

 

[Emmily]

*bump*

 

[Emmily]

*bump*

 

[Emmily]

Here is another example if i was to run the code on 05/04/11 i would expect these yellow rows

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	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly
Sheet2

If i run the code on the 15/04/11 i would expect these yellow rows

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	29/04/2009	Weekly
3	Mr	Christopher	Bloggs	Test2	5,830.00	50	6/04/2009	Weekly
4	Mrs	Brie	Masters	Test3	28,633.44	500	8/09/2010	Monthly
5	Mrs	Ben	Buckingham	Test4	800	200	22/03/2011	Monthly
6	Mr	Damien	Cot	Test5	4,400.00	440	22/03/2011	Monthly
7	Mr	Peter	Pesh	Test6	17,811.00	100	4/03/2011	Weekly
8	Mr	Neil	Smith	Test7	1,803.00	910	7/07/2011	Quarterly
9	Mr	Grant	Denyer	Test8	3,960.00	330	8/03/2011	Weekly
10	Mr	Steven	Gerrard	Test9	8,400.00	700	8/03/2011	Monthly
11	Mr	Guy	Seb	Test10	4,020.00	100	22/03/2011	Fortnightly
Sheet2

 

[Emmily]

*bump*

 

[shg]

Emmily, can you explain the exact logic you used in highlighting those rows based on those dates?