lookup function or table from dates that the overhead changed

[Richard]

I need to add an overhead rate (OHR) column to my purchases table.

The purchases table contains:
item
cost
date

The OHR will come from the OH table, which lists the new OHR every time it
is changed

The OH table looks like this
1 Jan 09 - 50%
7 Feb 09 - 51%
2 Mar 09 - 53%
etc.

So for example, for a purchase made on 10 Feb 09, the OHR would have been
51% since it

changed to this value 3-days earlier on 7 Feb 09.

Everyday I pull the data for these two tables from our Data Warehouse, and
use macros to format final results - so I need a macro to generate either a
lookup function or table to give me the OHR for any date based on the data in
the OH table?

Note: My purchase data spans ~15-years, and the OHR changes about 3-times
per year. So a

lookup table would have something like 15*365=5,474 rows. A lookup function,
maybe a

if-then construct, would have around 15*3=45 lines of code.

 

[Joel]

I find when writting VBA code and I need to perform a lookup that is not
exact I usually write a formula to the worksheet or use the evealuate function

1) formula method

'get last row of OHR table
with sheets("OHR")
LastOHR = Range("A" & rows.count).end(xlup).row
end with

MyDate = datevalue("10 Feb 09")
'get last row of purchase table
with sheets("Purchase Table")
Lastrow = .Range("A" & rows.count).end(xlup).row
.Range("D2").formula = "
"=Vlookup(OHR!$A1:$B" & LastOSH & "," & MyDate & ",2)"
'copy the formula down th eworksheet
.Range("D2").copy _
Destination:=.Range("D2" & LastRow)
end with


If you don't want to use a formula then use evaluate

ReturnValue = evaluate("Vlookup(OHR!$A1:$B" & LastOSH & "," & MyDate & ",2)")