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.

"EricG" wrote:

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

Eric

"Cathy Landry" wrote:

Hi Eric,

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

"EricG" wrote:

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" wrote:

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