Hi,

I'm having problems trying to match dates using VLookup in a Macro
any help will be much appreciated!

I have two worksheets. The first worksheet (Prices) has two columns
the first having a list of dates, and the second having correspondin
prices (numbers). The second worksheet (cleanPrices) has a set of date
(overlapping partially with the dates in the Prices spreadsheet. I wan
to match up prices for each of the dates in the cleanPrices spreadshee
based on the prices mentioned in the Prices worksheet.

To make it clear:
Price worksheet has:
Date Price
7/7/2005 100.75
7/6/2005 98.50
7/1/2005 99.00

cleanPrices has
Date Price
7/6/2005
7/1/2005
6/30/2005

I want the Price colum in cleanPrices to have 99.50, 99.00 and "#NA"

I am using the following code (or something like this),

Dim r1 As Range
Dim x As Integer

Set r1 = Worksheets("Price").Range("A2:B5")

For x = 1 to 3
Worksheets("cleanPrices").Range("B1").Offset(x, 0).Value
Application.WorksheetFunction.VLookup(Worksheets(" cleanPrices").Range("B1").Offset(x
0).Value, Worksheets("Price").Range(r1),2, False)
Next

I have tried several combinations (used DateValue and the likes, but
am always getting errors.

Thanks

--
eager_beave
-----------------------------------------------------------------------
eager_beaver's Profile: http://www.excelforum.com/member.php...fo&userid=2551
View this thread: http://www.excelforum.com/showthread.php?threadid=38956

Don't know but thinking with you:
First we may rewrite the same code, more clearly:

r1 = "A2:B5"
Range(r1)
or
set R1 = Worksheets("Price").Range("A2:B5")
(why naming a fixed a r1 variable and not really using it ?)

Then you could do the same with a r2 and
Worksheets("cleanPrices").Range("B1")

Then using a with:

with Application.WorksheetFunction
For x = 1 to 3
r2.Offset(x, 0).Value
.VLookup(r2.Offset(x, 0).Value, r1 ,2, False)
Next
end with

this is not so important for only a 3 cells loop, but this is a great
occasion to learn. This way is faster and easy to read (so to debug)


and then, you are getting errors... dates are not easy numbers to be
find: try to forget the r2.Offset(x, 0).Value and repalce it by
r2.Offset(x, 0) (put a range and not a value in the function)

So, excel will convert the proper way the date values

This works for me

Dim r1 As Range
Dim x As Long

Set r1 = Worksheets("Price").Range("A2:B5")

For x = 2 To 4
With Worksheets("cleanPrices")
.Range("B" & x).Value = Application.VLookup( _
.Range("A" & x), r1, 2, False)
End With
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)


"eager_beaver"
wrote in message
news:eager_beaver.1smaea_1122113110.0127@excelforu m-nospam.com...

Hi,

I'm having problems trying to match dates using VLookup in a Macro -
any help will be much appreciated!

I have two worksheets. The first worksheet (Prices) has two columns;
the first having a list of dates, and the second having corresponding
prices (numbers). The second worksheet (cleanPrices) has a set of dates
(overlapping partially with the dates in the Prices spreadsheet. I want
to match up prices for each of the dates in the cleanPrices spreadsheet
based on the prices mentioned in the Prices worksheet.

To make it clear:
Price worksheet has:
Date Price
7/7/2005 100.75
7/6/2005 98.50
7/1/2005 99.00

cleanPrices has
Date Price
7/6/2005
7/1/2005
6/30/2005

I want the Price colum in cleanPrices to have 99.50, 99.00 and "#NA"

I am using the following code (or something like this),

Dim r1 As Range
Dim x As Integer

Set r1 = Worksheets("Price").Range("A2:B5")

For x = 1 to 3
Worksheets("cleanPrices").Range("B1").Offset(x, 0).Value =

Application.WorksheetFunction.VLookup(Worksheets(" cleanPrices").Range("B1").
Offset(x,
0).Value, Worksheets("Price").Range(r1),2, False)
Next

I have tried several combinations (used DateValue and the likes, but I
am always getting errors.

Thanks!


--
eager_beaver
------------------------------------------------------------------------
eager_beaver's Profile:
http://www.excelforum.com/member.php...o&userid=25515
View this thread: http://www.excelforum.com/showthread...hreadid=389567

.Range("A" & x), r1, 2, False)

see: no .value in the function

Can you explain that one?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" wrote in message
...

.Range("A" & x), r1, 2, False)

see: no .value in the function

I think his error was to use a range().value inside the VLookup
because with a date, it's difficult to be sure to have exactly the
same number (and I think the error source is because the Vlookup do not
find any equal value). Letting Excel reading the cell (it knows it's a
date) make it do a better compare method.

With simple numbers (integers) the method with .value is ok. With date
It happens to have errors (none without the .value : since the dates are
in the list). I think his problem is around this .value inside the vlookup

There were a few problems

Application.WorksheetFunction.VLookup

was one

Range("B1").Offset(x,0).Value

was another, and

Range(r1)

was another one

but I don't think it was anything to do with .Range. I used that in the
solution I posted which works fine. Why do you mean by '... with a date,
it's difficult to be sure to have exactly the same number ...'?

Did you try the solution I posted, it works fine, regardless of the fact
that they are dates.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"abcd" wrote in message
...
I think his error was to use a range().value inside the VLookup
because with a date, it's difficult to be sure to have exactly the
same number (and I think the error source is because the Vlookup do not
find any equal value). Letting Excel reading the cell (it knows it's a
date) make it do a better compare method.

With simple numbers (integers) the method with .value is ok. With date
It happens to have errors (none without the .value : since the dates are
in the list). I think his problem is around this .value inside the vlookup