VBA to apply exchange rate until there is a change in exchange rate

[shafiq2]

Hi,

I have a list of date starts from 1 Jan 2014, 2-Jan2014, 3-Jan-2014 to 31-Dec 2014 and I get monthly or weekly new exchange rate, I want a VBA or excel funtion to apply the exchange rate of (for example) 31 Dec 2013 until the exchange rate is changed in a specific date for example 1 Feb 2014. So the exchange rate of 31-Dec-2013 can be used until 31-Jan-2014 because on 1-Feb-2014 the rate is changed,


[TABLE="width: 208"]
<tbody>[TR]
[TD="align: right"]0.759[/TD]
[TD="align: right"]01-Sep-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.748[/TD]
[TD="align: right"]01-Aug-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.736[/TD]
[TD="align: right"]30-Jun-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.735[/TD]
[TD="align: right"]01-Jun-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.723[/TD]
[TD="align: right"]01-May-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.727[/TD]
[TD="align: right"]01-Apr-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]01-Mar-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]01-Feb-14[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]31-Dec-13[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]


[TABLE="width: 300"]
<tbody>[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]31/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]01/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]02/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]03/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]04/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]05/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]06/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]07/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]08/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]09/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]10/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]11/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]12/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]13/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]14/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]15/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]16/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]17/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]18/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]19/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]20/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]21/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]22/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]23/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]24/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]25/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]26/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]27/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]28/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]29/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]30/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.725[/TD]
[TD="align: right"]31/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]01/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]02/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]03/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]04/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]05/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]06/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]07/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]08/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]09/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]10/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]11/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]12/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]13/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]14/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]15/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]16/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]17/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]18/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]19/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]20/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]21/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]22/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]23/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]24/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]25/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]26/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]27/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.737[/TD]
[TD="align: right"]28/02/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]01/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]02/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]03/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]04/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]05/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]06/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]07/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]08/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]09/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]10/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]11/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]12/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]13/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]14/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]15/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]16/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]17/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]18/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]19/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]20/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]21/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]22/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]23/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]24/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]25/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]26/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]27/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]28/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]29/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]30/03/2014[/TD]
[/TR]
[TR]
[TD="align: right"]0.731[/TD]
[TD="align: right"]31/03/2014
[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Special-K99]

Put the first table in REVERSE order from E1:F9

With your two columns in A and B
in C1
=OFFSET(F$1,MATCH(B1,F$1:F$9,1)-1,-1)
and copy down the column

 

[shafiq2]

Many Thanks, this is what I was looking for, it is much appreciated.