Hello Tom, your formula below is great, but i loop down the spreadsheet
"LCS_Complete_E90" and return the the date for the applicable number.
Could you please assist me to use this in this function ?

.................................................. .......

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

What's the question? How to loop

Range("X1").Select
Do
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-23],LCS_Complete_E90.xls!C1:C12,2,0)"
ActiveCell.NumberFormat = "dd-mmm-yy"
activeCell.Offset(1,0).Select
Loop until isempty(activecell.offset(0,-23))

--
Regards,
Tom Ogilvy




"Tempy" wrote in message
...
Hello Tom, your formula below is great, but i loop down the spreadsheet
"LCS_Complete_E90" and return the the date for the applicable number.
Could you please assist me to use this in this function ?

.................................................. ......

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

Thanks for your patience and assistance Tom.

Tempy

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

Thanks for your patience and assistance Tom.

Tempy

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

Hi Tom,

Thanks for all the help, it is very much appreciated. Sorry for all the
probably very simple questions.
The loop you gave me is super, but i also need to change the date format
on the the first sheet (Text:20050501)as it loops down with the Vlookup
and past it to the new sheet as (01-May-05). As per the original query
and i do not know how to piece it all together.

If i am not clear, please let me know. There is a big delay in the
replies as i am in South Africa

Tempy

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