Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in Varying Object Names
Hello:
I have a workbook which reads and summarizes data from anywhere from 2 to 7 workbooks. I want to set up a single For-Next Loop and some other statements to prevent me from hardcoding six different cases. I need to know what is the proper syntax for referring to worksheet objects whose names can vary. Example: Assume that the target workbook (which contains the code) is named TARGET.xls. The worksheet object where the data will be summarized is called DATASUM (a worksheet within TARGET). The user chooses the number of books (2 to 7) to read data from, and this variable is called NUMBEROFBOOKS. Assume that the data source books are named GROUP1.xls, GROUP2.xls, ... GROUP7.xls. There is a worksheet in each of the workbooks called DATA. I have chosen to name those worksheet objects as follows: GRP1DATA, GRP2DATA ... GRP7DATA. Here is an example of what I need to know: For N = 2 to NUMBEROFBOOKS .... DATASUM.Cells(1,1) = DATASUM.Cells(1,1) + GRP{&N&}DATA.Cells(1,1) ' What is the proper syntax for varying the object name within a loop like this? - brackets {} added to make it easier to spot. .... Next N I hope this is clear. I know there are other ways of doing this, but this is a very simplified example of just one thing I need to do. if someone can advise me of the proper syntax, I think I can work the rest of it out. Late thought: Maybe I answered my own question. Is something like this legitimate? (I haven't tried it): "GRP"&N&"DATA".Cells(1,1) Thanks, MARTY |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax in Varying Object Names
Follow up:
"GRP"&N&"DATA".Cells(1,1) is not legit. It yields an expecting end of statement error. I also tried moving the N to the end, i.e.: "GRPDATA"&N.Cells(1,1). This doesn't work either. It's expecting an object called N. Any other ideas? I'm getting really curious as to whether this can actually be done. "Marty" wrote: Hello: I have a workbook which reads and summarizes data from anywhere from 2 to 7 workbooks. I want to set up a single For-Next Loop and some other statements to prevent me from hardcoding six different cases. I need to know what is the proper syntax for referring to worksheet objects whose names can vary. Example: Assume that the target workbook (which contains the code) is named TARGET.xls. The worksheet object where the data will be summarized is called DATASUM (a worksheet within TARGET). The user chooses the number of books (2 to 7) to read data from, and this variable is called NUMBEROFBOOKS. Assume that the data source books are named GROUP1.xls, GROUP2.xls, ... GROUP7.xls. There is a worksheet in each of the workbooks called DATA. I have chosen to name those worksheet objects as follows: GRP1DATA, GRP2DATA ... GRP7DATA. Here is an example of what I need to know: For N = 2 to NUMBEROFBOOKS ... DATASUM.Cells(1,1) = DATASUM.Cells(1,1) + GRP{&N&}DATA.Cells(1,1) ' What is the proper syntax for varying the object name within a loop like this? - brackets {} added to make it easier to spot. ... Next N I hope this is clear. I know there are other ways of doing this, but this is a very simplified example of just one thing I need to do. if someone can advise me of the proper syntax, I think I can work the rest of it out. Late thought: Maybe I answered my own question. Is something like this legitimate? (I haven't tried it): "GRP"&N&"DATA".Cells(1,1) Thanks, MARTY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba syntax for excel object | Excel Discussion (Misc queries) | |||
Sum prices 4 object made up of varying number of components | Excel Worksheet Functions | |||
lookup needed for names in varying order | Excel Worksheet Functions | |||
Parsing Full Names of varying lenths and parts | Excel Programming | |||
Proper syntax to Set a Workbook Object? | Excel Programming |