Using a variabel to make an array?
 

I´ve worked up this code so far:

Sub PrintFormat()


Dim ws As Worksheet
Dim prt1 As String
Dim prt2 As String
Dim StrArray() As String

prt1 = ""

For Each ws In Worksheets

If InStr(1, ws.Name, "F") 1 Then
prt1 = prt1 & ", " & """" & ws.Name & """"
End If

Next ws

prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)

'Worksheets("ark3").Range("A1") = prt
'"O-002F","O-003F"
Sheets(Array("O-002F", "O-003F")).Select 'this one works
Sheets(Array((prt1))).Select 'this one doesnt work
Sheets(Array(("&prt2"))).Select 'this one doesnt work
End Sub

While debugging it says "Subscript out of range" on either of th
arrays that are done over the variables prt1 and prt2, but holding th
cursor over the variables they seem to be strings with the same conten
as is in the first array. The problem is that over time a lot mor
sheets will be added, and all sheets containging the letter f must b
in the array.

I am sorry if my english is bad ;

--
Message posted from http://www.ExcelForum.com

Using a variabel to make an array?
 

I don't understand what you are doing with Prt2.

Specific to your question...

The documentation on the select method -- if you checked it -- leaves
something to be desired.

Try:

Sheets(prt1).Select
Sheets(prt2).Select(False)

Is the intent to select all the worksheets? If so, use something like:

Sub testIt3()
Dim i As Integer
Sheets(1).Select
For i = 2 To Sheets.Count
Sheets(i).Select False
Next i
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , SkatKat
says...
I=3Fve worked up this code so far:

Sub PrintFormat()


Dim ws As Worksheet
Dim prt1 As String
Dim prt2 As String
Dim StrArray() As String

prt1 = ""

For Each ws In Worksheets

If InStr(1, ws.Name, "F") 1 Then
prt1 = prt1 & ", " & """" & ws.Name & """"
End If

Next ws

prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)

'Worksheets("ark3").Range("A1") = prt
'"O-002F","O-003F"
Sheets(Array("O-002F", "O-003F")).Select 'this one works
Sheets(Array((prt1))).Select 'this one doesnt work
Sheets(Array(("&prt2"))).Select 'this one doesnt work
End Sub

While debugging it says "Subscript out of range" on either of the
arrays that are done over the variables prt1 and prt2, but holding the
cursor over the variables they seem to be strings with the same content
as is in the first array. The problem is that over time a lot more
sheets will be added, and all sheets containging the letter f must be
in the array.

I am sorry if my english is bad ;)


---
Message posted from http://www.ExcelForum.com/

Using a variabel to make an array?
 

As you see, your approach doesn't work. You can't build a string that
resembles a valid argument to the array function and convert it to an array
using the Array function.

Sub PrintFormat()


Dim ws As Worksheet
Dim StrArray() As String
Dim i As Long


i = 0
For Each ws In Worksheets

If InStr(1, ws.Name, "F") 1 Then
ReDim Preserve StrArray(0 To i)
StrArray(i) = ws.Name
Debug.Print i, ws.Name, StrArray(i)
i = i + 1
End If

Next ws
Sheets(StrArray).Select

' or
' Sheets(StrArray).Printout
' without selecting

End Sub




"SkatKat " wrote in message
...
I´ve worked up this code so far:

Sub PrintFormat()


Dim ws As Worksheet
Dim prt1 As String
Dim prt2 As String
Dim StrArray() As String

prt1 = ""

For Each ws In Worksheets

If InStr(1, ws.Name, "F") 1 Then
prt1 = prt1 & ", " & """" & ws.Name & """"
End If

Next ws

prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)

'Worksheets("ark3").Range("A1") = prt
'"O-002F","O-003F"
Sheets(Array("O-002F", "O-003F")).Select 'this one works
Sheets(Array((prt1))).Select 'this one doesnt work
Sheets(Array(("&prt2"))).Select 'this one doesnt work
End Sub

While debugging it says "Subscript out of range" on either of the
arrays that are done over the variables prt1 and prt2, but holding the
cursor over the variables they seem to be strings with the same content
as is in the first array. The problem is that over time a lot more
sheets will be added, and all sheets containging the letter f must be
in the array.

I am sorry if my english is bad ;)


---
Message posted from http://www.ExcelForum.com/

Using a variabel to make an array?
 

Hi
not really sure what you're trying to do but you're not
really assigning variables to an array but simply
combining strings to a longer string. Maybe the following
is what you're looking for:
Sub ws_in_array()
Dim ws_array()
Dim wks_counter As Integer
Dim i
Dim s

wks_counter = ActiveWorkbook.Worksheets.Count
ReDim ws_array(1 To wks_counter)
s = 0
For i = 1 To wks_counter
If InStr(Worksheets(i).Name, "t") 0 Then
s = s + 1
ws_array(s) = Worksheets(i).Name
End If
Next
ReDim Preserve ws_array(1 To s)
For i = 1 To UBound(ws_array)
MsgBox ws_array(i)
Next
End Sub



-----Original Message-----
I´ve worked up this code so far:

Sub PrintFormat()


Dim ws As Worksheet
Dim prt1 As String
Dim prt2 As String
Dim StrArray() As String

prt1 = ""

For Each ws In Worksheets

If InStr(1, ws.Name, "F") 1 Then
prt1 = prt1 & ", " & """" & ws.Name & """"
End If

Next ws

prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)

'Worksheets("ark3").Range("A1") = prt
'"O-002F","O-003F"
Sheets(Array("O-002F", "O-003F")).Select 'this one works
Sheets(Array((prt1))).Select 'this one doesnt work
Sheets(Array(("&prt2"))).Select 'this one doesnt work
End Sub

While debugging it says "Subscript out of range" on
either of the
arrays that are done over the variables prt1 and prt2,
but holding the
cursor over the variables they seem to be strings with
the same content
as is in the first array. The problem is that over time a
lot more
sheets will be added, and all sheets containging the
letter f must be
in the array.

I am sorry if my english is bad ;)


---
Message posted from http://www.ExcelForum.com/

.

Using a variabel to make an array?
 

"SkatKat " wrote in message
...
prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)


What is this supposed to do.


'Worksheets("ark3").Range("A1") = prt
'"O-002F","O-003F"
Sheets(Array("O-002F", "O-003F")).Select 'this one works
Sheets(Array((prt1))).Select 'this one doesnt work

Assuming you cut out those two lines above, this will work

Sheets(Array(SPlit(prt1,"&"))).Select

Sheets(Array(("&prt2"))).Select 'this one doesnt work

No chance, none of the worksheet start with &.