Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
I'm trying to create an array based on the names of the worksheets in a
workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub THANK YOU!! -- Jack of all trades... master of none.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
try this
Sub sheetnames() For Each WS In Sheets WS.Range("a10") = WS.Index Next End Sub -- Don Guillett SalesAid Software "Alex" wrote in message ... I'm trying to create an array based on the names of the worksheets in a workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub THANK YOU!! -- Jack of all trades... master of none.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
The code I have I know is a little on the confusing side.
This should be an array that stores the worksheet name. I'm trying to get the array to be variable based on the workbook index. "Don Guillett" wrote: try this Sub sheetnames() For Each WS In Sheets WS.Range("a10") = WS.Index Next End Sub -- Don Guillett SalesAid Software "Alex" wrote in message ... I'm trying to create an array based on the names of the worksheets in a workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub THANK YOU!! -- Jack of all trades... master of none.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
You might want to explain what are you trying to do *without* including
the how. Do you want the names of all worksheets in certain cells in a specific worksheet? Or, something else? What you are doing is violating a bunch of programming and mathematical rules. Even if you got it to work, your results would be that a different cell on each worksheet would contain the name of that worksheet. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I'm trying to create an array based on the names of the worksheets in a workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub THANK YOU!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
There are several problems:
1) Dim Wrksht(num) Num must be a constant, known at the time the code is compiled. It can't be the name of a variable. 2) Wrksht(Num) = WS.Name + 1 The worksheet name is a string. You can't add 1 to text. 3) Same line: You keep putting the name in the same (last) element of the array, overwriting what was there before. 4) If your point is to put the names onto the worksheet, one at a time, there's no point in also saving them in an array. See 2nd routine. Sub test2() Dim wrksht() As String Dim num As Long Dim WS As Worksheet Dim Sh As Long num = ActiveWorkbook.Worksheets.Count Redim wrksht(1 to num) Sh = 0 For Each WS In Worksheets Sh = Sh + 1 wrksht(Sh) = WS.name Range("A10").Offset(Sh - 1, 0).Formula = wrksht(Sh) Next WS End Sub OR, dispense with the array: Sub test2() Dim S As Long For S = 1 To ActiveWorkbook.Worksheets.Count Range("A10").Offset(S - 1, 0).Value = Worksheets(S).Name Next S End Sub In article , says... I'm trying to create an array based on the names of the worksheets in a workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating an array based on worksheet index
Trust me I know there are "issues" with the code... it was a "work" in
progress. But you did answer the most important question, I need to have a constant for the array variable. The reason for populating the cells with the worksheet names was to verify that the array was being populated with the correct data. "Myrna Larson" wrote: There are several problems: 1) Dim Wrksht(num) Num must be a constant, known at the time the code is compiled. It can't be the name of a variable. 2) Wrksht(Num) = WS.Name + 1 The worksheet name is a string. You can't add 1 to text. 3) Same line: You keep putting the name in the same (last) element of the array, overwriting what was there before. 4) If your point is to put the names onto the worksheet, one at a time, there's no point in also saving them in an array. See 2nd routine. Sub test2() Dim wrksht() As String Dim num As Long Dim WS As Worksheet Dim Sh As Long num = ActiveWorkbook.Worksheets.Count Redim wrksht(1 to num) Sh = 0 For Each WS In Worksheets Sh = Sh + 1 wrksht(Sh) = WS.name Range("A10").Offset(Sh - 1, 0).Formula = wrksht(Sh) Next WS End Sub OR, dispense with the array: Sub test2() Dim S As Long For S = 1 To ActiveWorkbook.Worksheets.Count Range("A10").Offset(S - 1, 0).Value = Worksheets(S).Name Next S End Sub In article , says... I'm trying to create an array based on the names of the worksheets in a workbook, using the index as the variable. I keep getting a "constant" error, can I create a array this way? Sub test2() Dim wrksht(num) Dim num As Byte Dim WS As Worksheet num = ActiveWorkbook.Worksheets.Count For Each WS In Worksheets wrksht(num) = WS.name + 1 Range("A10").Formula = wrksht(num) ActiveCell.Offset(1, 0).Select Next WS End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CREATING FORMULA IN ONE WORKSHEET BASED ON MULTIPLE CRITERIA IN AN | Excel Worksheet Functions | |||
Creating/Naming New Worksheets Based on Select Cells in Master Worksheet | Excel Worksheet Functions | |||
Creating an Index | Excel Worksheet Functions | |||
Creating and Index with worksheet names | Excel Discussion (Misc queries) | |||
Creating a worksheet with values based on a control cell | Excel Programming |