Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure what to name this subject
Hello:
If N is an integer, is the following syntax (&N) allowed in workbook names, objects, etc? Example: If I write this: Set MYSHEET&N = Workbooks(Group&N.xls).Sheets ("SheetName"). and then I set N = 1, will VBA interpret this as: Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") ? If not (i.e., it doesn't like that), does anyone know the correct syntax for something like this? Would I have to use "..." anywhere in order for it to be correct? Thanks, MARTY |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure what to name this subject
You should set the value for N before using it in a statement
N=1 Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets("SheetName"). Will interpret as: MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") Notice the space between Group and 1 and using N outside of quotes. Mike F "MARTY" wrote in message ... Hello: If N is an integer, is the following syntax (&N) allowed in workbook names, objects, etc? Example: If I write this: Set MYSHEET&N = Workbooks(Group&N.xls).Sheets ("SheetName"). and then I set N = 1, will VBA interpret this as: Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") ? If not (i.e., it doesn't like that), does anyone know the correct syntax for something like this? Would I have to use "..." anywhere in order for it to be correct? Thanks, MARTY |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correction
I got a little sloppy
Set MYSHEET&N = Workbooks("Group "&N&".xls").Sheets("SheetName"). Another ampersand will follow a variable if it is in the middle of a name. Mike F "Mike Fogleman" wrote in message news:cmUFc.23065$XM6.15333@attbi_s53... You should set the value for N before using it in a statement N=1 Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets("SheetName"). Will interpret as: MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") Notice the space between Group and 1 and using N outside of quotes. Mike F "MARTY" wrote in message ... Hello: If N is an integer, is the following syntax (&N) allowed in workbook names, objects, etc? Example: If I write this: Set MYSHEET&N = Workbooks(Group&N.xls).Sheets ("SheetName"). and then I set N = 1, will VBA interpret this as: Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") ? If not (i.e., it doesn't like that), does anyone know the correct syntax for something like this? Would I have to use "..." anywhere in order for it to be correct? Thanks, MARTY |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correction
Thank you Mike.
-----Original Message----- I got a little sloppy Set MYSHEET&N = Workbooks("Group "&N&".xls").Sheets ("SheetName"). Another ampersand will follow a variable if it is in the middle of a name. Mike F "Mike Fogleman" wrote in message news:cmUFc.23065$XM6.15333@attbi_s53... You should set the value for N before using it in a statement N=1 Set MYSHEET&N = Workbooks("Group "&N".xls").Sheets ("SheetName"). Will interpret as: MYSHEET1 = Workbooks (Group1.xls).Sheets("SheetName") Notice the space between Group and 1 and using N outside of quotes. Mike F "MARTY" wrote in message ... Hello: If N is an integer, is the following syntax (&N) allowed in workbook names, objects, etc? Example: If I write this: Set MYSHEET&N = Workbooks(Group&N.xls).Sheets ("SheetName"). and then I set N = 1, will VBA interpret this as: Set MYSHEET1 = Workbooks(Group1.xls).Sheets ("SheetName") ? If not (i.e., it doesn't like that), does anyone know the correct syntax for something like this? Would I have to use "..." anywhere in order for it to be correct? Thanks, MARTY . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure what to name this subject
Marty,
You need Set MYSHEET&N = Workbooks("Group" & N & ".xls").Sheets("SheetName") N is a VBA variable, the book name is a string, so you need to concatenate the values into your string. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "MARTY" wrote in message ... Hello: If N is an integer, is the following syntax (&N) allowed in workbook names, objects, etc? Example: If I write this: Set MYSHEET&N = Workbooks(Group&N.xls).Sheets ("SheetName"). and then I set N = 1, will VBA interpret this as: Set MYSHEET1 = Workbooks(Group1.xls).Sheets("SheetName") ? If not (i.e., it doesn't like that), does anyone know the correct syntax for something like this? Would I have to use "..." anywhere in order for it to be correct? Thanks, MARTY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Charts and Charting in Excel | |||
No Subject | Excel Worksheet Functions | |||
No Subject | Excel Discussion (Misc queries) |