Mileage Rates

C

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
 
B

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
 
E

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
 
C

Cathy Landry

Hi Eric,

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

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.
 
E

EricG

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

Eric
 
C

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.
 
J

Joel

Use worksheet function like this

Dim Rate As Variant

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Keep unique values 1
VB Code 4
sumproduct with vlookup 3
no. of months worked between two dates, etc... 0
match function with nested if 12
functions in criteria 5
Looking at alternatives 1
Value of prepayment 2

Top