| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formatting of numbers within a string | Excel Worksheet Functions | |||
| Hiding an SQL connection string in an Excel macro | Excel Programming | |||
| Excel VBA to search all macro code in Excel module for specific string criteria | Excel Programming | |||
| Special string formatting | Excel Programming | |||
| Use Word VBA string in Excel macro? | Excel Programming | |||
Linear Mode
