if vlookup is returning a cell reference, then
sStr1 = Replace(sStr1,"A1",sStr1)
should be
sStr1 = Replace(sStr1,"A1",sStr)
Other than that, I don't know what you are asking.
--
Regards,
Tom Ogilvy
"Tempy" wrote in message
...
Hi Tom,
You gave me the code below for a previous query, i have an entire column
(K) which needs the format changing and copying, i use a loop with a
vlookup, it could be anything from 10 to 5000 rows.
That is because the date isn't being stored as a date I would suspect -
it
is being stored as a number or as text.
You would have to break it into pieces and make it a date
assume the value is in A1
=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))
would be the basic formula. You now need to replace A1 with the location
of
the cell
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)"
sStr = "VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)"
sStr1 = "=DATE(--(LEFT(A1,4)),--MID(A1,5,2),--RIGHT(A1,2))"
sStr1 = Replace(sStr1,"A1",sStr1)
ActiveCell.FormulaR1C1 = sStr1
ActiveCell.NumberFormat = "dd-mmm-yy"
--
Regards,
Tom Ogivy
Tempy
*** Sent via Developersdex http://www.developersdex.com ***