VBA lookup dates and return <today

M

Mr Muir

New Member
Joined
Aug 15, 2013
Messages
9
Hi all

i have a spreadsheet that has various column with dates in and other columns with names in.

i would like a macro to look through each column one row at a time, check for dates less than today and present a message box to say "x persons name has a deadline due" and add that date that is <today's date.

i am unable to post the data due to confidentiality.

i currently use a simple method of selecting various ranges and find the minimum and an if statement that checks it's < today. This then returns a message that says a deadline has been breached.

I would like like this to be more specific.

thanks in advance
 
Sort by date Sort by votes
Mr Muir said:
Hi all

i have a spreadsheet that has various column with dates in and other columns with names in.

i would like a macro to look through each column one row at a time, check for dates less than today and present a message box to say "x persons name has a deadline due" and add that date that is <TODAY'S date.

i am unable to post the data due to confidentiality.

i currently use a simple method of selecting various ranges and find the minimum and an if statement that checks it's < today. This then returns a message that says a deadline has been breached.

I would like like this to be more specific.

thanks in advance
Click to expand...
The data content is irrelevant, but where the type of data is located is essential in writing code. You will need to either give a good narrative description of which columns contain dates and which contain names, or create a mock up illustration of the data and post that. Otherwise you might not get a suitable response to your issue.
 
Upvote 0
As JLGWhiz said, I need to know what columns have dates and what columns have the name to return. Other than that, I just need to know how you want it to be output. A msgbox won't do (probably) because it might return a few hundred overdue items (in theory, I bet your data isn't that large). I would probably output it to a new sheet or a temp workbook so it can be reviewed/printed/etc.
 
Upvote 0
Thanks

I will mock up a spread sheet in tomorrow and we can go from there. I will zero out any confidential data.
 
Upvote 0
Assume this as a data table in Excel and we can find the Name or Fruit or vegetable if the given date is Today. A VBA code is embedded below, you can try it today (25th July 2014) else try with making one of these date to current.

Date
Fruits & Vegs
09-Aug-13
Apple
27-Mar-14
Banana
07-May-13
Cabbage
29-Mar-14
Date
07-Jun-13
Elderberry
19-Mar-14
Fig
31-Aug-13
Guava
04-Nov-13
Haricot
25-Jul-14
Iceberg Lettuce
10-Apr-14
Jackfruit
14-Apr-14
Kiwi
08-Dec-13
Litchi
25-Jul-14
Mango
16-Oct-13
Nutmeg
11-Feb-14
Orange
18-Sep-13
Peach
15-Nov-13
Quince
22-May-13
Raddish
18-Jul-13
Sunflower

<tbody>
</tbody>


Sub Button1_Click()


On Error Resume Next


Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Dim X As Long

For X = 1 To LR

If Cells(X, 1).Value = Date Then
MsgBox (Cells(X, 2).Value & " has a deadline due")
End If

Next X



End Sub
 
Upvote 0
HI See below table.

I need to Set up a breach warning for each client who has a date ,today in columns H, I, M, N, O, Q, R, S, T, U, V.

This spreadsheet is updated by the admin on a daily basis so we should only have a breach where somthing has been overlooked and this doent occur very often.

I need a vba to check though each of these columns and if a breach ocurrs then a message box apears to say that ie. "a Client" has a breach in "AGM Deadline" and the Date that is causing the breach.

I do not need a button as i set this to run on Auto open.

I hope this clarifies my requirments if not i will answer your qeries.

Thanks Guys

[TABLE="width: 4323"]
<TBODY>[TR]
[TD]Client</SPAN>[/TD]
[TD]Op Exec</SPAN>[/TD]
[TD]Admin</SPAN>[/TD]
[TD]Incorp.</SPAN>[/TD]
[TD]Internal Number Number</SPAN>[/TD]
[TD]Reg Number</SPAN>[/TD]
[TD]Year End</SPAN>[/TD]
[TD]AGM Deadline</SPAN>[/TD]
[TD]Regulator Deadlines</SPAN>[/TD]
[TD]Auditors</SPAN>[/TD]
[TD]Board Meeting 1 Dates</SPAN>[/TD]
[TD]Board Meeting 2 Dates</SPAN>[/TD]
[TD]Board Meeting 3 Dates</SPAN>[/TD]
[TD]Accounts Frequency</SPAN>[/TD]
[TD]GFSC Loan Renewals</SPAN>[/TD]
[TD]Insurance Renewal 1</SPAN>[/TD]
[TD]Insurance Renewal 2</SPAN>[/TD]
[TD]Insurance Renewal 3</SPAN>[/TD]
[TD]Tax Payable Date 1</SPAN>[/TD]
[TD]Tax Payable Date 2</SPAN>[/TD]
[TD]IPT Payable</SPAN>[/TD]
[TD]Regulator Quarterly/Annual Returns</SPAN>[/TD]
[/TR]
[TR]
[TD]Client1</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]29 January 2003</SPAN>[/TD]
[TD]905</SPAN>[/TD]
[TD]41382</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]18 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly 8 days</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client2</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]18 November 1994</SPAN>[/TD]
[TD]115</SPAN>[/TD]
[TD]28847</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]18 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]PWC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly 8th each Month</SPAN>[/TD]
[TD]30 July 2015</SPAN>[/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client3</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]12 January 1978</SPAN>[/TD]
[TD] [/TD]
[TD]6648</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]13 October 2014</SPAN>[/TD]
[TD]Monthly 2 working day</SPAN>[/TD]
[TD] [/TD]
[TD]28 February 2015</SPAN>[/TD]
[TD]15 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]30 June 2015</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual Return</SPAN>[/TD]
[/TR]
[TR]
[TD]Client4</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]17 February 1977</SPAN>[/TD]
[TD] [/TD]
[TD]5821</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly 2 working days</SPAN>[/TD]
[TD] [/TD]
[TD]28 February 2015</SPAN>[/TD]
[TD]15 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]30 June 2015</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]NO</SPAN>[/TD]
[TD]AnnualAccounts</SPAN>[/TD]
[/TR]
[TR]
[TD]Client5</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]01 February 2004</SPAN>[/TD]
[TD]908</SPAN>[/TD]
[TD]41598</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]31 January 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly 10th Working Day</SPAN>[/TD]
[TD] [/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client6</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]29 January 1981</SPAN>[/TD]
[TD]48</SPAN>[/TD]
[TD]9488</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]17 December 2014</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]Saffrey Champness</SPAN>[/TD]
[TD]01 September 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Annually July 9th working day</SPAN>[/TD]
[TD] [/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client7</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]17 March 2010</SPAN>[/TD]
[TD]963</SPAN>[/TD]
[TD]51617</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]N/A</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD]22 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]Quarterly 6 working days</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client8</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]29 February 2012</SPAN>[/TD]
[TD]965</SPAN>[/TD]
[TD]5744</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]14 November 2014</SPAN>[/TD]
[TD]Monthly 1 working day</SPAN>[/TD]
[TD] [/TD]
[TD]28 February 2015</SPAN>[/TD]
[TD]15 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]30 June 2015</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]NO</SPAN>[/TD]
[TD]Quarterly (2 mth after)</SPAN>[/TD]
[/TR]
[TR]
[TD]Client9</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]23 December 2005</SPAN>[/TD]
[TD] [/TD]
[TD]44134</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]10 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD] [/TD]
[TD]11 November 2014</SPAN>[/TD]
[TD] [/TD]
[TD]Quarterly 5th Day (incl Weekends)</SPAN>[/TD]
[TD] [/TD]
[TD]01 May 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client10</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]24 October 1995</SPAN>[/TD]
[TD]769</SPAN>[/TD]
[TD]30137</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]12 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Deloitte</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17 September 2014</SPAN>[/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD]01 November 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]31 March 2014</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client11</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]24 March 2006</SPAN>[/TD]
[TD]916</SPAN>[/TD]
[TD]44578</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client12</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin1</SPAN>[/TD]
[TD]08 February 2008</SPAN>[/TD]
[TD] [/TD]
[TD]48494</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]19 August 2014[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Deloitte</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly last day of month</SPAN>[/TD]
[TD] [/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client13</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]25 January 1994</SPAN>[/TD]
[TD]754</SPAN>[/TD]
[TD]27727</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 July 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Lince Salisbury</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Annual</SPAN>[/TD]
[TD] [/TD]
[TD]01 May 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client14</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]11 December 1998</SPAN>[/TD]
[TD]771</SPAN>[/TD]
[TD]34726</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]Waived</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Ernst & Young</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client15</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]22 August 1995</SPAN>[/TD]
[TD]26</SPAN>[/TD]
[TD]29891</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]01 September 2014</SPAN>[/TD]
[TD]31 May 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD]26 September 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD]14 August 2014[/TD]
[TD]01 February 2015</SPAN>[/TD]
[TD]01 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]YES</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client16</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]09 August 1994</SPAN>[/TD]
[TD]755</SPAN>[/TD]
[TD]28467</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]20 August 2014[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]PWC</SPAN>[/TD]
[TD]March TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Annual</SPAN>[/TD]
[TD] [/TD]
[TD]01 November 2014</SPAN>[/TD]
[TD]01 December 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client17</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]28 March 1985</SPAN>[/TD]
[TD]753</SPAN>[/TD]
[TD]13927</SPAN>[/TD]
[TD]31-Mar</SPAN>[/TD]
[TD]01 September 2014</SPAN>[/TD]
[TD]31 July 2014[/TD]
[TD]PWC</SPAN>[/TD]
[TD]May TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]YES</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client18</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]16 July 1993</SPAN>[/TD]
[TD]76</SPAN>[/TD]
[TD]27050</SPAN>[/TD]
[TD]31-Mar</SPAN>[/TD]
[TD]12 November 2014</SPAN>[/TD]
[TD]31 July 2014[/TD]
[TD]Deloitte LLP</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD]22 October 2014</SPAN>[/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]YES</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client19</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]31-Mar</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]N/A</SPAN>[/TD]
[TD]N/A</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]N/A</SPAN>[/TD]
[TD]N/A</SPAN>[/TD]
[/TR]
[TR]
[TD]Client20</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin2</SPAN>[/TD]
[TD]23 June 2000</SPAN>[/TD]
[TD]880</SPAN>[/TD]
[TD]37010</SPAN>[/TD]
[TD]30-Jun</SPAN>[/TD]
[TD]20 September 2014</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD]26 June 2014[/TD]
[TD]01 July 2014[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Paid by Group</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client21</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]18 March 2013</SPAN>[/TD]
[TD]973</SPAN>[/TD]
[TD]56409</SPAN>[/TD]
[TD]30 June 2014</SPAN>[/TD]
[TD]09/06/2015.</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 June 2014[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]NO</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client22</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]29 June 2012</SPAN>[/TD]
[TD]987</SPAN>[/TD]
[TD]38933</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]24 September 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD]01 June 2015</SPAN>[/TD]
[TD]01 October 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client23</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]03 February 1998</SPAN>[/TD]
[TD]988</SPAN>[/TD]
[TD]33577</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]23 August 2014[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client24</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]09 November 2005</SPAN>[/TD]
[TD]918</SPAN>[/TD]
[TD]43896</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]17 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]PwC</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Bi-ann</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client25</SPAN>[/TD]
[TD]Op Exec1</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]09 November 2005</SPAN>[/TD]
[TD]919</SPAN>[/TD]
[TD]43897</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]17 October 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]PwC</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Bi-ann</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client26</SPAN>[/TD]
[TD]Op Exec2</SPAN>[/TD]
[TD]Admin3</SPAN>[/TD]
[TD]04 October 2004</SPAN>[/TD]
[TD]912</SPAN>[/TD]
[TD]42386</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]25 November 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Cleland & Co Limited</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]01 June 2014[/TD]
[TD]01 April 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]YES</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client27</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]21 September 2001</SPAN>[/TD]
[TD]37155</SPAN>[/TD]
[TD]38761</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]23 April 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27 October 2014</SPAN>[/TD]
[TD]Monthly</SPAN>[/TD]
[TD]31 May 2014[/TD]
[TD]01 October 2014</SPAN>[/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD]##############</SPAN>[/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client28</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]02 October 2009</SPAN>[/TD]
[TD]40088</SPAN>[/TD]
[TD]50963</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]24 September 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD]27 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client29</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]20 December 2002</SPAN>[/TD]
[TD]37610</SPAN>[/TD]
[TD]40361</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]18 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD]23 September 2014</SPAN>[/TD]
[TD]10 December 2014</SPAN>[/TD]
[TD]Quarterly MCR's</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client30</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]14 June 1996</SPAN>[/TD]
[TD]35230</SPAN>[/TD]
[TD]31068</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]08 April 2015</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quartelry</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client31</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]05 June 1975</SPAN>[/TD]
[TD] [/TD]
[TD]4624</SPAN>[/TD]
[TD]28-Feb</SPAN>[/TD]
[TD]01 February 2015</SPAN>[/TD]
[TD]30 June 2015</SPAN>[/TD]
[TD]PwC</SPAN>[/TD]
[TD] [/TD]
[TD]15 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD]27 June 2014[/TD]
[TD]04 March 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client32</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]19 January 2006</SPAN>[/TD]
[TD]38736</SPAN>[/TD]
[TD]44220</SPAN>[/TD]
[TD]30-Sep</SPAN>[/TD]
[TD]21 August 2014[/TD]
[TD]31 January 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client33</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]22 November 2002</SPAN>[/TD]
[TD]37582</SPAN>[/TD]
[TD]40243</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD]TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client34</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]17 January 1997</SPAN>[/TD]
[TD]2001</SPAN>[/TD]
[TD]31930</SPAN>[/TD]
[TD]31-Jan</SPAN>[/TD]
[TD] [/TD]
[TD]31 May 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD]March - TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Client35</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]08 December 1995</SPAN>[/TD]
[TD]35041</SPAN>[/TD]
[TD]30330</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD] [/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]BDO</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client36</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]02 April 1993</SPAN>[/TD]
[TD]34061</SPAN>[/TD]
[TD]26685</SPAN>[/TD]
[TD]05-Apr</SPAN>[/TD]
[TD] [/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Lince Salisbury</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]24 September 2014</SPAN>[/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client37</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]28 January 1988</SPAN>[/TD]
[TD]32170</SPAN>[/TD]
[TD]18204</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Deloitte</SPAN>[/TD]
[TD]March - TBC</SPAN>[/TD]
[TD]27 November 2014</SPAN>[/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD]31 March 2015</SPAN>[/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD]01 March 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client38</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]15 December 1985</SPAN>[/TD]
[TD]31396</SPAN>[/TD]
[TD]14881</SPAN>[/TD]
[TD]31-Dec</SPAN>[/TD]
[TD]11 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]Deloitte</SPAN>[/TD]
[TD]19/03/2014 - TBC</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Bi-annually</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client39</SPAN>[/TD]
[TD]Op Exec3</SPAN>[/TD]
[TD]Admin4</SPAN>[/TD]
[TD]29 June 2000</SPAN>[/TD]
[TD]36706</SPAN>[/TD]
[TD]37022</SPAN>[/TD]
[TD]30-Jun</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]Saffrey Champness</SPAN>[/TD]
[TD]N/A</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Bi-annually</SPAN>[/TD]
[TD] [/TD]
[TD]01 July 2014[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client40</SPAN>[/TD]
[TD]Op Exec4</SPAN>[/TD]
[TD]Admin5</SPAN>[/TD]
[TD]23 August 1996</SPAN>[/TD]
[TD] [/TD]
[TD]31365</SPAN>[/TD]
[TD]30 April 2014</SPAN>[/TD]
[TD]02 December 2014</SPAN>[/TD]
[TD]31 August 2014</SPAN>[/TD]
[TD]BDO Limited</SPAN>[/TD]
[TD]TBA</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]01 October 2014</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client41</SPAN>[/TD]
[TD]Op Exec4</SPAN>[/TD]
[TD]Admin5</SPAN>[/TD]
[TD]19 September 1985</SPAN>[/TD]
[TD] [/TD]
[TD]14527</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]31 October 2014</SPAN>[/TD]
[TD]Littlejohn LLP</SPAN>[/TD]
[TD]Tba</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client42</SPAN>[/TD]
[TD]Op Exec4</SPAN>[/TD]
[TD]Admin5</SPAN>[/TD]
[TD]07 March 2005</SPAN>[/TD]
[TD] [/TD]
[TD]42903</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]PWC CI Ltd</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]15 May 2015</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]No</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client43</SPAN>[/TD]
[TD]Op Exec4</SPAN>[/TD]
[TD]Admin5</SPAN>[/TD]
[TD]26 November 1993</SPAN>[/TD]
[TD] [/TD]
[TD]27539</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]PWC CI Ltd</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]08 November 2014</SPAN>[/TD]
[TD]01 November 2014</SPAN>[/TD]
[TD]01 May 2015</SPAN>[/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
[TR]
[TD]Client44</SPAN>[/TD]
[TD]Op Exec4</SPAN>[/TD]
[TD]Admin5</SPAN>[/TD]
[TD]15 December 1983</SPAN>[/TD]
[TD] [/TD]
[TD]12416</SPAN>[/TD]
[TD]##############</SPAN>[/TD]
[TD]31 December 2014</SPAN>[/TD]
[TD]30 April 2015</SPAN>[/TD]
[TD]KPMG Channel Islands Limited</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Monthly</SPAN>[/TD]
[TD] [/TD]
[TD]01 March 2015</SPAN>[/TD]
[TD]01 January 2015</SPAN>[/TD]
[TD] [/TD]
[TD]Quarterly</SPAN>[/TD]
[TD] [/TD]
[TD]Yes</SPAN>[/TD]
[TD]Annual</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=2><COL><COL><COL><COL><COL><COL><COL><COL span=3><COL><COL><COL span=3><COL><COL><COL><COL></COLGROUP>[/TABLE]
Client Op Exec Admin Incorp. Internal Number Number Reg Number Year End AGM Deadline Regulator Deadlines Auditors Board Meeting 1 Dates Board Meeting 2 Dates Board Meeting 3 Dates Accounts Frequency GFSC Loan Renewals Insurance Renewal 1 Insurance Renewal 2 Insurance Renewal 3 Tax Payable Date 1 Tax Payable Date 2 IPT Payable Regulator Quarterly/Annual Returns
Client1 Op Exec1 Admin1 29 January 2003 905 41382 31-Dec 18 July 2015 30 April 2015 Ernst & Young Monthly 8 days 01 January 2015 NO Annual
Client2 Op Exec1 Admin1 18 November 1994 115 28847 31-Dec 18 July 2015 30 April 2015 PWC Monthly 8th each Month 30 July 2015 01 July 2015 NO Annual
Client3 Op Exec1 Admin1 12 January 1978 6648 31-Dec 01 July 2015 30 April 2015 Ernst & Young 13 October 2014 Monthly 2 working day 28 February 2015 15 October 2014 30 June 2015 31 December 2014 NO Annual Return
Client4 Op Exec1 Admin1 17 February 1977 5821 31-Dec 01 July 2015 30 April 2015 Ernst & Young Monthly 2 working days 28 February 2015 15 October 2014 30 June 2015 31 December 2014 NO AnnualAccounts
Client5 Op Exec2 Admin1 01 February 2004 908 41598 31-Dec 31 January 2015 30 April 2015 KPMG Quarterly 10th Working Day 01 April 2015 NO Annual
Client6 Op Exec1 Admin1 29 January 1981 48 9488 31-Dec 17 December 2014 31 October 2014 Saffrey Champness 01 September 2014 Annually July 9th working day 01 July 2015 Yes Annual
Client7 Op Exec1 Admin1 17 March 2010 963 51617 31-Dec N/A 30 April 2015 Ernst & Young 22 October 2014 Quarterly 6 working days 30 September 2014 01 April 2015 NO Annual
Client8 Op Exec1 Admin1 29 February 2012 965 5744 31-Dec 01 July 2015 30 April 2015 Ernst & Young 14 November 2014 Monthly 1 working day 28 February 2015 15 October 2014 30 June 2015 31 December 2014 NO Quarterly (2 mth after)
Client9 Op Exec1 Admin1 23 December 2005 44134 31-Dec 10 July 2015 30 April 2015 BDO 11 November 2014 Quarterly 5th Day (incl Weekends) 01 May 2015 NO Annual
Client10 Op Exec2 Admin1 24 October 1995 769 30137 31-Dec 12 July 2015 30 April 2015 Deloitte 17 September 2014 Quarterly 01 January 2015 01 November 2014 31 March 2014 Annual
Client11 Op Exec1 Admin1 24 March 2006 916 44578 31-Dec 01 December 2014 30 April 2015 BDO Quarterly 01 April 2015 NO Annual
Client12 Op Exec1 Admin1 08 February 2008 48494 31-Dec 19 August 2014 30 April 2015 Deloitte Quarterly last day of month 01 April 2015 NO Annual
Client13 Op Exec2 Admin2 25 January 1994 754 27727 31-Dec 01 July 2015 30 April 2015 Lince Salisbury Annual 01 May 2015 NO Annual
Client14 Op Exec2 Admin2 11 December 1998 771 34726 31-Dec Waived 30 April 2015 Ernst & Young Quarterly NO Annual
Client15 Op Exec1 Admin2 22 August 1995 26 29891 31-Dec 01 September 2014 31 May 2015 BDO 26 September 2014 Quarterly 14 August 2014 01 February 2015 01 October 2014 YES Annual
Client16 Op Exec2 Admin2 09 August 1994 755 28467 31-Dec 20 August 2014 30 April 2015 PWC March TBC Annual 01 November 2014 01 December 2014 NO Annual
Client17 Op Exec2 Admin2 28 March 1985 753 13927 31-Mar 01 September 2014 31 July 2014 PWC May TBC Quarterly 01 April 2015 YES Annual
Client18 Op Exec1 Admin2 16 July 1993 76 27050 31-Mar 12 November 2014 31 July 2014 Deloitte LLP TBC TBC 22 October 2014 Quarterly 01 April 2015 YES Annual
Client19 Op Exec2 Admin2 31-Mar N/A N/A Quarterly N/A N/A
Client20 Op Exec2 Admin2 23 June 2000 880 37010 30-Jun 20 September 2014 31 October 2014 KPMG TBC Quarterly 26 June 2014 01 July 2014 Paid by Group Annual
Client21 Op Exec1 Admin3 18 March 2013 973 56409 30 June 2014 09/06/2015. 31 October 2014 KPMG TBC Quarterly 01 June 2014 NO Annual
Client22 Op Exec1 Admin3 29 June 2012 987 38933 31 December 2014 24 September 2014 30 April 2015 KPMG Monthly 01 January 2015 01 June 2015 01 October 2015 Annual
Client23 Op Exec1 Admin3 03 February 1998 988 33577 31 December 2014 23 August 2014 30 April 2015 KPMG Quarterly 01 January 2015 Annual
Client24 Op Exec1 Admin3 09 November 2005 918 43896 31 December 2014 17 October 2014 PwC TBC Bi-ann No Annual
Client25 Op Exec1 Admin3 09 November 2005 919 43897 31 December 2014 17 October 2014 30 April 2015 PwC TBC Bi-ann No Annual
Client26 Op Exec2 Admin3 04 October 2004 912 42386 31 December 2014 25 November 2014 30 April 2015 Cleland & Co Limited TBC Monthly 01 June 2014 01 April 2015 YES Annual
Client27 Op Exec3 Admin4 21 September 2001 37155 38761 31-Dec 23 April 2015 30 April 2015 KPMG 27 October 2014 Monthly 31 May 2014 01 October 2014 01 January 2015 31 December 2014 No Annual
Client28 Op Exec3 Admin4 02 October 2009 40088 50963 31-Dec 24 September 2014 30 April 2015 KPMG 27 October 2014 Monthly 01 January 2015 No Annual
Client29 Op Exec3 Admin4 20 December 2002 37610 40361 31-Dec 18 December 2014 30 April 2015 KPMG 23 September 2014 10 December 2014 Quarterly MCR's 01 January 2015 No Annual
Client30 Op Exec3 Admin4 14 June 1996 35230 31068 31-Dec 08 April 2015 30 April 2015 KPMG Quartelry 01 January 2015 Yes Annual
Client31 Op Exec3 Admin4 05 June 1975 4624 28-Feb 01 February 2015 30 June 2015 PwC 15 October 2014 Monthly 27 June 2014 04 March 2015 No Annual
Client32 Op Exec3 Admin4 19 January 2006 38736 44220 30-Sep 21 August 2014 31 January 2015 BDO TBC Monthly 01 January 2015 No Annual
Client33 Op Exec3 Admin4 22 November 2002 37582 40243 31-Dec 31 December 2014 30 April 2015 BDO TBC Monthly No Annual
Client34 Op Exec3 Admin4 17 January 1997 2001 31930 31-Jan 31 May 2015 BDO March - TBC Quarterly 01 October 2014 Yes
Client35 Op Exec3 Admin4 08 December 1995 35041 30330 31-Dec 30 April 2015 BDO Quarterly No Annual
Client36 Op Exec3 Admin4 02 April 1993 34061 26685 05-Apr 30 April 2015 Lince Salisbury 24 September 2014 Quarterly 01 October 2014 Yes Annual
Client37 Op Exec3 Admin4 28 January 1988 32170 18204 31-Dec 31 December 2014 30 April 2015 Deloitte March - TBC 27 November 2014 Quarterly 31 March 2015 01 January 2015 01 March 2015 No Annual
Client38 Op Exec3 Admin4 15 December 1985 31396 14881 31-Dec 11 December 2014 30 April 2015 Deloitte 19/03/2014 - TBC Bi-annually No Annual
Client39 Op Exec3 Admin4 29 June 2000 36706 37022 30-Jun 31 December 2014 31 October 2014 Saffrey Champness N/A Bi-annually 01 July 2014 No Annual
Client40 Op Exec4 Admin5 23 August 1996 31365 30 April 2014 02 December 2014 31 August 2014 BDO Limited TBA Quarterly 01 October 2014 Quarterly Yes Annual
Client41 Op Exec4 Admin5 19 September 1985 14527 31 October 2014 31 December 2014 31 October 2014 Littlejohn LLP Tba Quarterly No Annual
Client42 Op Exec4 Admin5 07 March 2005 42903 31 December 2013 31 December 2014 30 April 2015 PWC CI Ltd Monthly 15 May 2015 No Annual
Client43 Op Exec4 Admin5 26 November 1993 27539 31 December 2013 31 December 2014 30 April 2015 PWC CI Ltd Monthly 08 November 2014 01 November 2014 01 May 2015 Quarterly Yes Annual
Client44 Op Exec4 Admin5 15 December 1983 12416 31 December 2013 31 December 2014 30 April 2015 KPMG Channel Islands Limited Monthly 01 March 2015 01 January 2015 Quarterly Yes Annual
 
Upvote 0
You can put this in your ThisWorkbook code module and it should run on each workbook open event. This will check each column of the eleven specified columns for a date preceding the current date. If a condition is found, it will put the date and cell location in a holding varible until all columns have been checked. Once all columns are checked, the message box will display listing all of the dates and cell locations which require attention.
Code: 
Private Sub Workbook_Open()
Dim sh As Worksheet, lr As Long, cnt As Long, cAry As Variant, c As Range, loc As String
Set sh = Sheets(1) 'Edit sheet name
cAry = Array("H", "I", "M", "N", "O", "Q", "R", "S", "T", "U", "V")
    For i = LBound(cAry) To UBound(cAry)
        lr = sh.Cells(Rows.Count, cAry(i)).End(xlUp).Row
            For Each c In Range(sh.Cells(2, cAry(i)), sh.Cells(lr, cAry(i)))
                If c.Value < Date And c <> "" Then
                    loc = loc & c.Value & " at Cell " & c.Address & ", "
                End If
            Next
    Next
MsgBox "A Client has a breach as follows: " & loc
End Sub
 
Upvote 0
Many Thanks

I have the following VBA running

Code: 
 Sub Auto_Open()

Dim lr As Long
Row = ActiveSheet.UsedRange.Rows.Count
Dim X As Long
Dim Y As Long
Dim OutApp As Object
Dim OutMail As Object

' Macro to go throug AGM dealines and advise of Due dates

For X = 1 To Row

If Cells(X, 10).Value = "" Then GoTo 10

If Cells(X, 10).Value <= Date Then

If (MsgBox(Cells(X, 1).Value & " has a " & Cells(1, 10).Value & " due on " & Cells(X, 10).Value & ". Do you want to send an email to " & Cells(X, 3) & " to inform them that a Dedline is due?", vbYesNo)) _
= vbNo Then GoTo 10
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error GoTo 10
        
        With OutMail
            .To = Cells(X, 4).Value
            .CC = ""
            .BCC = ""
            .Subject = Cells(X, 1).Value & " Important Client Deadline Information. Do not reply!"
            .Body = Cells(X, 1).Value & " has a " & Cells(1, 10).Value & " due. Please go to the Client deadlines spreadsheet and update the feilds that have been actioned"
            .Send
        End With

End If

10

Next X
 
End Sub

This gives me the result i need but i have to copy and paste this for each column at the moment to change context eg column 10 is AGM deadlines and colum n 12 is regulator deadlines. IS there any way that after the rows have been cheked my column number will advance to specific column numbers?


I may have to even group my dates in the data set.


Thank you
 
Upvote 0
You can try this to see if it advances the columns for you. I did not test it, so hopefully no typos. Couple of housekeeping changes.
Code: 
Sub Auto_Open()
Dim lr As Long
Row = ActiveSheet.UsedRange.Rows.Count
Dim X As Long
Dim Y As Long
Dim OutApp As Object
Dim OutMail As Object, cAry As Variant
' Macro to go throug AGM dealines and advise of Due dates
cAry = Array("H", "I", "M", "N", "O", "Q", "R", "S", "T", "U", "V")
    For i = LBound(cAry) To UBound(cAry)
        For X = 1 To Row
            If Cells(X, cAry(i)).Value = "" Then GoTo SKIP:
            If Cells(X, cAry(i)).Value <= Date Then
            If (MsgBox(Cells(X, 1).Value & " has a " & Cells(1, cAry(i)).Value & " due on " & Cells(X, cAry(i)).Value _
                & ". Do you want to send an email to " & Cells(X, 3) & " to inform them that a Dedline is due?", vbYesNo)) _
                = vbNo Then GoTo SKIP:
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                On Error GoTo SKIP:
                With OutMail
                    .To = Cells(X, 4).Value
                    .CC = ""
                    .BCC = ""
                    .Subject = Cells(X, 1).Value & " Important Client Deadline Information. Do not reply!"
                    .Body = Cells(X, 1).Value & " has a " & Cells(1, cAry(i)).Value _
                    & " due. Please go to the Client deadlines spreadsheet and update the feilds that have been actioned"
                    .Send
                End With
            End If
SKIP:
        Next X
    Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
EMAIL REMINDERS FROM EXCEL VBA
Replies
2
Views
367
Logit
Logit
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
336
JAZ91
J
M
  • Solved
Format cells if a cell has todays date, otherwise remain default.
Replies
5
Views
219
MeaclH
M
C
  • Solved
Record date cell data is changed - not VBA
Replies
4
Views
322
chally3
C
C
  • Question Question
Lookup 2 criteria and return yes or no
Replies
3
Views
382
RoryA
RoryA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top