Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mileage Rates
Use worksheet function like this Dim Rate As Variant Rate = Application.WorksheetFunction.VLookup( _ Range("A2"), _ Range("D2:E11"), _ 2, True) "Cathy Landry" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm using the mileage log., How do I total all the mileage? | New Users to Excel | |||
mileage | Excel Worksheet Functions | |||
Gas Mileage | Excel Discussion (Misc queries) | |||
service invoice for parts, tax, two labor rates,mileage inventory. | Excel Discussion (Misc queries) | |||
gas and mileage | Excel Programming |