Solved

Excel VBA - How to loop through a range and compare dates

Posted on 2014-04-14
7
1,381 Views
Last Modified: 2014-04-14
Good day

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?

Many thanks

Rob
0
Comment
Question by:KempstonIT
  • 3
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39998746
You dont' really need VBA for this one.

You could use a SUMIFS.

See the attached workbook for example.
Example.xlsx
0
 

Author Comment

by:KempstonIT
ID: 39998790
Hi Steve

Thanks for the example. It's not calculating correctly though. For example invoice IN187649 for vehice BF72YEGP should add up to 5.64 as all the toll charges fall within the contract start and end date.

I've never done a SUMIFS before, I'll try and get my head around it though.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39998871
Ah missed some dollar signs out... try the following:

=SUMIFS($D$2:$D$8,$B$2:$B$8,K2,$C$2:$C$8,">="&I2,$C$2:$C$8,"<="&J2)

or

=SUMIFS(D:D,B:B,K2,C:C,">="&I2,C:C,"<="&J2)

This second formula should make more sense.
They both do the same, but the second looks at the whole column rather than a specific range or rows.
Example.xlsx
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 45

Expert Comment

by:aikimark
ID: 39998872
Do lots of testing, since you're using European dates (d/m/yyyy) instead of US dates (m/d/yyyy).  This difference can cause unusual behavior.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39998886
Indeed, date formats can be the curse of Excel some times.
I prefer dates in format "dd mmm yyyy" as they are easier to manage.

As for the SUMIFS formula.. a breakdown...

=SUMIFS(D:D,  = Sum up column D (Cost)
B:B, K2,             = Where column B is equal to K2 (Registration Number)
C:C, ">="&I2,    = Where column C is Greater or Equal to I2 (Start Date)
C:C,"<="&J2)    = Where column C is Less Than or Equal to J2 (End Date)
0
 

Author Comment

by:KempstonIT
ID: 39999213
Thanks Steve, I've managed to get it working with SUMIFS but am battling with the dates!!! Any suggestions on formatting them?
0
 

Author Comment

by:KempstonIT
ID: 40000946
All sorted, I had to format the date using the following formula:
=DATEVALUE(CONCATENATE(TEXT(DAY(N5),"0#"),"/",TEXT(MONTH(N5),"0#"),"/",YEAR(N5)))
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question