Tom Ogilvy - More help please
 

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

Tom Ogilvy - More help please
 

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

Tom Ogilvy - More help please
 

Thanks for your patience and assistance Tom.

Tempy

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

Tom Ogilvy - More help please
 

Thanks for your patience and assistance Tom.

Tempy

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

Tom Ogilvy - More help please
 

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