VBA To Decide Which Hourly Rate

[Guest]

Hello there,

I've got two databases in Ms Excel 2000.

The first (Sheet 1) contains; Name-Workdate-Hours
The Second (Sheet 2) Contains; Name-Hourly Rate Period 1-Hourly Rate Period
2-Hourly Rate Period 3

As I tried to match those using a single formula; I stumbled upon some
difficulties. Thus I decided to look for a way to make a macro pick the
needed formula.

As Sheet 1 is filled with information; in collumn 'S' I want to fill in the
Formula from Sheets(Sheet3).Range("S1") if the Date in Collumn 'E' is in
Period 1; Sheets(Sheet3).Range("S2") if the Date in Collumn 'E' is in Period
2, etc...

Does anyone have clue how I can make this work? Thanks in advance!

 

[Arvi Laanemets]

Hi

An example from one of my workbooks, where this task is done by worksheet
functions only.

Sheet: Rates
ArticleID, ValidFrom, RateValue, RateValueConverted, ValidTo

ArticleID - an unique string value
ValidFrom - a date (starting day of period, for which the given rate was/is
valid)
RateValue - a numeric entry
RateValueConverted - a simple formula which converts RateValue (you can drop
this column, but you have to adjust formulas then)
ValidTo - a date (ending day of period, for which the given rate was/is
valid). Its calculated by formula. When there is same article with bigger
start time present, then ValidTo equals the later starting time minus 1,
otherwise it is current system day. The formula for cell E2 is:

=IF(OR(A2="",B2="",B2>TODAY()),"",IF(ISERROR(MATCH(A2,OFFSET(A2,1,,COUNTA(Ra
teArt),),0)),TODAY(),IF(OFFSET(B2,MATCH(A2,OFFSET(A2,1,,COUNTA(RateArt),),0)
,)>0,OFFSET(B2,MATCH(A2,OFFSET(A2,1,,COUNTA(RateArt),),0),)-1,"")))

where RateArt is a dynamic range
=INDEX(RatesTbl,,1)
where RatesTbl is a dynamic range
=OFFSET(Rates!$A$2,,,COUNTIF(Rates!$A:$A,"<>")-1,5)

Sheet: Production
*, Date, *, Machine, *, *, *, Article, Quantity, Rate, ...
(* marks columns not used in formula below)

The rate in cell J4 (rows 1:3 are table header) is calculated by formula
=IF(OR(B4="",D4="",H4=""),"",SUMPRODUCT(--(RateArt=H4),--(RateFrom<=B4),--(R
ateTo>=B4),RateValue))
where RateFrom, RateTo and RateValue are dynamic named ranges
RateFrom=INDEX(RatesTbl,,2)
RateTo=INDEX(RatesTbl,,3)
RateValue=INDEX(RatesTbl,,5)