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.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Cathy Landry" wrote in message
...
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