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
|