Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy - More help please
Thanks for your patience and assistance Tom.
Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy - More help please
Thanks for your patience and assistance Tom.
Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Thanks to Tom Ogilvy | Excel Programming | |||
Thank You Tom Ogilvy | Excel Worksheet Functions | |||
Tom Ogilvy | Excel Programming | |||
Thank u Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming |