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

Puts single quotes around the sheetname which is required for sheet names
containing a space.

--
Regards,
Tom Ogilvy

"Rain" wrote in message
...
Note: The actual sheet name has a space in it. Ex: My sheet name is "Sheet
Name"
I have illustrated this as Sheet1 in my previous note.
Could this be a problem ?

"Rain" wrote:

Thanks for the response. however, I still seem to have a problem with
this:
Macro Excerpt:
-----------------
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LEFT(RIGHT(""0""&" & strSheetName&
"!RC2,8),2)&""/""&MID(RIGHT(""0""&" & strSheetName &
"!RC2,8),3,2)&""/""&RIGHT(" & strSheetName & "!RC2,4))"

Immediate Window:
-----------------------
?ActiveCell.FormulaR1C1

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

When the code gets executed, at this stage, a dialog box comes up asking
the
user to enter the file name ! Is it because the double quotes around "
0 "
and " / " has gone missing? How do I rectify this?

TIA


"Tom Ogilvy" wrote:

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.