E
eager_beaver
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
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