Mileage Rates

[Cathy Landry]

Hello,

I run a monthly mileage report that contains transaction date, this date
determines the rate per mile our company reimburses employees. Is there a
way via VBA to pull the correct rate based on trans dt? The example below
shows the trans date and rate based on our company's rate.



TRANS DATE RATE EFF DT RATE
6/15/2009 0.46 1/1/2005 0.405
5/12/2009 0.45 9/1/2005 0.485
9/2/2005 0.485 1/1/2006 0.445
2/8/2009 0.55 1/1/2007 0.485
3/10/2009 0.45 1/1/2008 0.505
3/9/2009 0.55 7/1/2008 0.585
1/1/2009 0.55
3/10/2009 0.45
6/1/2009 0.46
7/1/2009 0.48

Thank you in advance
Cathy

 

[Bernard Liengme]

I am troubled by two things:
a) the top of the table has 4 columns, the bottom only 2, and
b) the dates in column A seem to be randomly ordered

If we had a table with two columns and A had dates in order (ascending or
descending), then we could call VLOOKUP in the VBA code to find the rate.
Can you give more info?

best wishes

 

[EricG]

Assuming your Transaction Dates are in column A, the Effective Dates are in
column D, and the published rates are in column E (all with headers as
shown), then the formula to get the rate for a particular date (which you
would put in column B starting at cell B2) is:

=VLOOKUP($A2,$D$2:$E$11,2)

Drag this down as far as you have Transaction Dates.

Note that VLookup will find the item that is closest but SMALLER than the
item you are searching for, so in this case it finds the Effective Date that
is less than or equal to the Transaction Date. This seems to be the correct
way of looking up the rates - they are good from the time they are published
until the date a new rate is published.

HTH,

Eric

 

[Cathy Landry]

Hi Eric,

I am using a vlookup now, but would like to automate this via vba if possible.

 

[Cathy Landry]

Hi Bernard,

cola is the transaction date from my report, colb shows the $rate per mile
based on a vlookup from colc and cold......would like to automate this via
vba if possible.

 

[EricG]

Can you explain a little what you mean by automate? That might help us
provide a better solution.

Eric

 

[Cathy Landry]

Hi Eric,

Well, ideally I would run this report and the end user would click a macro
button that will populate the rate per mile based on the transaction date in
the report. I would prefer not to have to manipulate the spreadsheet as I can
have the report run and pushed out to the end user automatically. Hope that
makes sense.

 

[Joel]

Use worksheet function like this

Dim Rate As Variant

Rate = Application.WorksheetFunction.VLookup( _
Range("A2"), _
Range("D2:E11"), _
2, True)