View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
abcd[_3_] abcd[_3_] is offline
external usenet poster
 
Posts: 17
Default Handling Dates in VLookup

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