Excel VBA - How to loop through a range and compare dates
Posted on 2014-04-14
I have a client who hires out vehicles and needs to charge their client toll road charges. The scenario is as follows: a invoice is generated for each hire for the period of time the vehicle was hired out for. The company managing the toll roads sends my client a spreadsheet of all the toll points the companies vehicles went through over a specific period of time (normally monthly). The client then needs to calculate how much to charge their client for the tolls charge over the hire period.
I've got two lists that I need to work with, firstly a list from the toll company of vehicle registration numbers, the date and the charge. My client then has a list of invoices and with the start and end date of the hire.
The toll data looks like this:
Vehicle Date Charge
BD71MKGP 8/12/2013 3.3
BD75MKGP 8/12/2013 2.31
BD82XRGP 11/12/2013 2.43
BD82XRGP 11/12/2013 2.46
BF72YEGP 13/12/2013 0.6
BF72YEGP 13/12/2013 2.52
The client data looks like this:
Invoice Start Date End Date Vehicle
IN187626 06/12/2013 10/12/2013 BD71MKGP
IN187649 10/12/2013 13/12/2013 BF72YEGP
IN187819 11/12/2013 11/12/2013 BD82XRGP
So for example, if we take the first vehicle from the first list we would then have to search the second list to first find the list then to determine if the date from the first list falls between the start and end date of the row in the second list.
My VBA is very rusty and I'm not sure how to go about stepping through the second list and checking the date.
Could anybody give me a code example?