strSheetName = "Sheet1"
? "=DATEVALUE(LEFT(RIGHT(""0""&" & strSheetName &
"!RC2,8),2)&""/""&MID(RIGHT(""0""&" & strSheetName &
"!RC2,8),3,2)&""/""&RIGHT(" & strSheetName & "!RC2,4))"
=DATEVALUE(LEFT(RIGHT("0"&Sheet1!RC2,8),2)&"/"&MID(RIGHT("0"&Sheet1!RC2,8),3
,2)&"/"&RIGHT(Sheet1!RC2,4))

so

ActiveCell.FormulaR1C1 = "=DATEVALUE(LEFT(RIGHT(""0""&" & strSheetName &
"!RC2,8),2)&""/""&MID(RIGHT(""0""&" & strSheetName &
"!RC2,8),3,2)&""/""&RIGHT(" & strSheetName & "!RC2,4))"

worked for me.

--
Regards,
Tom Ogilvy


"Rain" wrote in message
...
Hi,

I am writing a macro in which I need to do the following:
Here I am passing the Sheet name as an argument to the macro.
If I replace the sheet name (Sheet1) with the variable (strSheetName), it
dosen't work
Actual:
--------
ActiveCell.FormulaR1C1 = _


"=DATEVALUE(LEFT(RIGHT(""0""&Sheet1!RC2,8),2)& ""/""&MID(RIGHT(""0""&Sheet1!R
C2,8),3,2)&""/""&RIGHT(Sheet1!RC2,4))"

Changed:
-----------
ActiveCell.FormulaR1C1 = _


"=DATEVALUE(LEFT(RIGHT(""0""&strSheetName!RC2,8),2 )&""/""&MID(RIGHT(""0""&st
rSheetName!RC2,8),3,2)&""/""&RIGHT(strSheetName!RC2,4))"

I tried to use the String Editor Add-in that gets packaged with VBA,
unfortunately I do not have it.

Could you please, let me know when I'm going wrong.

Thanks in advance.