ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel Macro string formatting (https://www.excelbanter.com/excel-programming/320895-excel-macro-string-formatting.html)

Rain
Excel Macro string formatting
 
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!RC2,8),3,2)&""/""&RIGHT(Sheet1!RC2,4))"

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

"=DATEVALUE(LEFT(RIGHT(""0""&strSheetName!RC2,8),2 )&""/""&MID(RIGHT(""0""&strSheetName!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.

Gary Brown[_8_]
Excel Macro string formatting
 
I think Range(strSheetName!RC2) should probably do it.
HTH,
Gary Brown


"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!RC2,8),3,2)&""/""&RIGHT(Sheet1!RC2,4))"

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

"=DATEVALUE(LEFT(RIGHT(""0""&strSheetName!RC2,8),2 )&""/""&MID(RIGHT(""0""&strSheetName!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.



Tom Ogilvy
Excel Macro string formatting
 
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.

Rain
Excel Macro string formatting
 
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.

Rain
Excel Macro string formatting
 
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.

Rain
Excel Macro string formatting + Worked !
 
Thanks for all your help. The sheet name was the problem.
I changed the macro to: (macro excerpt)
---------------------------
ActiveCell.FormulaR1C1 = _
"=DATEVALUE(LEFT(RIGHT(""0""&'" & strSheetName&
"'!RC2,8),2)&""/""&MID(RIGHT(""0""&" '& strSheetName &
"'!RC2,8),3,2)&""/""&RIGHT(" '& strSheetName & "'!RC2,4))"

Note: the sheet name will now be in " ' " (single quotes) as shown
above.

Thanks!

"Rain" wrote:

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.

Rain
Excel Macro string formatting + worked
 
The sheet name was the problem. I changed the macro to hold the sheet name
within single quotes and it worked.

Thanks!

"Rain" wrote:

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.

Tom Ogilvy
Excel Macro string formatting
 
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.


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com