Tom Ogilvy - More help please

[Tempy]

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"

 

[Tom Ogilvy]

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

 

[Tempy]

Thanks for your patience and assistance Tom.

Tempy

 

[Tempy]

Thanks for your patience and assistance Tom.

Tempy

 

[Tempy]

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