Change Date format
 

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 ***

Change Date format
 

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 ***

Change Date format
 

Hi Tom,

I am looping down a spreadsheet for anything between 10 and 5000 rows
copying from one spreadsheet to another using the vlookup to insert the
changed date into the new spreadsheet.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***