Using a variabel to make an array?

  • Thread starter Thread starter SkatKat
  • Start date Start date
S

SkatKat

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 ;
 
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
 
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
 
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
 
SkatKat > said:
prt1 = Mid(prt1, 3, Len(prt1))
prt2 = Mid(prt1, 2, Len(prt1) - 2)
Click to expand...

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
Click to expand...

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

Sheets(Array(SPlit(prt1,"&"))).Select
Sheets(Array(("&prt2"))).Select 'this one doesnt work
Click to expand...

No chance, none of the worksheet start with &.
 
Back
Top