Excel VBA: Missing dates in sequence that need to be poplulated 1

[EDGE99]

Hey Everyone,

I have a spreadsheet that contains two columns. Date and Currency
Rate Date Rate
10/1/2012 2.0264
10/2/2012 2.0268
10/3/2012 2.02585
10/4/2012 2.023
10/5/2012 2.018875
10/8/2012 2.0306
10/9/2012 2.0297
10/10/2012 2.0355
10/11/2012 2.041725
10/12/2012 2.0426
10/15/2012 2.0426
10/16/2012 2.0357
10/17/2012 2.033725

The problem with this workbook is that it only has Monday - Friday and is missing weekends. So I need to be able to add the Sat & Sun dates each time I run into this issue. I also need to pick the last rate that it found and populate it for Sat & Sun

Rate Date Rate
10/1/2012 2.0264
10/2/2012 2.0268
10/3/2012 2.02585
10/4/2012 2.023
10/5/2012 2.018875
[COLOR=##EF2929]10/6/2012[/color] 2.018875
[COLOR=##EF2929]10/7/2012[/color] 2.018875
10/8/2012 2.0306
10/9/2012 2.0297
10/10/2012 2.0355
10/11/2012 2.041725
10/12/2012 2.0426
[COLOR=##EF2929]10/13/2012[/color] 2.0462
[COLOR=##EF2929]10/14/2012[/color] 2.0462
10/15/2012 2.0426
10/16/2012 2.0357
10/17/2012 2.033725

I hope I have supplied enough information. I have a very large workbook to go through and would really like to be able to code this out if possible and save some time.

Thanks in advance

 

[SkipVought]

hi,

Please explain why this is a problem. It must be in the way that you want to reference this table, and you might not be familiar with other ways that would not need that

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I'd suggest using
[tt]
=INDEX(Rate,MATCH(D3,Rate_Date,1),1)
[/tt]
where D3 is your lookup date value

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EDGE99]

Hey Skip,

Ther problem is I have several years worth of currency that I have to go through and to manually add in each Sat & Sund would take forever to do. So I am looking to see if there is a way to identify the gap add the two addtional rows and then populate the data via VBA instead of manually doing so.

Thanks

 

[SkipVought]

THAT is your perceived problem!

Tell me WHY you need this data inserted? In other words what are you using that make you think that you need to add this data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EDGE99]

Because I have transactions that happen on those weekend days and I have to make sure the correct exchange rate is used in order to properly report the data. You have me intrigued a bit where are you going with this?

 

[SkipVought]

That is why I posted
[tt]
=INDEX(Rate,MATCH(D3,Rate_Date,1),1)
[/tt]
where if D3 contains a weekend date, you get the correct rate returned!

Using Named Ranges.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EDGE99]

So I used this method and it works! Thank you for making me think outside of the standard box. But I am still a bit confussed on how it works would you be able to explain how this works? Sorry I am no expert and would really like to understand this.

Thanks,

 

[SkipVought]

Sure.

Of course this has nothing to do with VBA, but rather these are Excel spreadsheet functions.

Both the MATCH() function and the VLOOKUP() function have an argument that allows for and EXACT MATCH lookup or a GREATER THAN lookup. MATCH() also has a LESS THAN lookup. For a number of reasons, I much prefer using MATCH() & INDEX() than VLOOKUP().

I would suggest looking at Excel HELP for additional information and examples of thes and other spreadsheet functions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EDGE99]

Skip

Thanks again I can see the power of using Index & Match now. So now if I wanted to utilize this for multiple countries at a time how would I handle it if I added an additional column Country

Brazil 10/1/2012 2.0264
Brazil 10/2/2012 2.0268
Brazil 10/3/2012 2.02585
Brazil 10/4/2012 2.023
Brazil 10/5/2012 2.018875
Brazil 10/8/2012 2.0306
Mexico 10/8/2012 2.0264
Mexico 10/9/2012 2.0268
Mexico 10/10/2012 2.02585
Mexico 10/11/2012 2.023
Mexico 10/12/2012 2.018875
Mexico 10/14/2012 2.0306

 

[SkipVought]

So now you have TWO lookup values. This will not work, using either MATCH() or VLOOKUP() UNLESS you make a KEY column, by concatenating the values in the columns you want to use as a lookup values. For instance

[pre]
Country Rate Date Rate KEY
Brazil 10/01/2012 2.0264 Brazil41183 [highlight #FCE94F]=A2&B2[/highlight]
Brazil 10/02/2012 2.0268 Brazil41184
Brazil 10/03/2012 2.02585 Brazil41185
Brazil 10/04/2012 2.023 Brazil41186
Brazil 10/05/2012 2.01888 Brazil41187
Brazil 10/08/2012 2.0306 Brazil41190
Mexico 10/08/2012 2.0264 Mexico41190
Mexico 10/09/2012 2.0268 Mexico41191
Mexico 10/10/2012 2.02585 Mexico41192
Mexico 10/11/2012 2.023 Mexico41193
Mexico 10/12/2012 2.01888 Mexico41194
Mexico 10/14/2012 2.0306 Mexico41196
[/pre]

Then just concatenate the country and DateValue as the lookup value

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EDGE99]

Brilliant! Thanks so much Skip! You just saved me a ton of work my friend!