ARRAYS

[Gary B]

Hi there,

Having a problem with the following code

Sub mcrPrint()
Dim prange As String

Sheets("macroinputs").Range("N1").Value = InputBox("Enter
Section No to print/preview or ALL", , "ALL")
Sheets("macroinputs").Range("N2").Value = InputBox("Print
(Y) or Preview (N)", , "N")
prange = Sheets("macroinputs").Range("O8").Value

If Sheets("macroinputs").Range("N1").Value <> "All"
Then
Sheets(Array(prange)).Select
If Sheets("macroinputs").Range("N2").Value = "Y"
Then
ActiveWindow.SelectedSheets.PrintOut
Copies:=1, Collate:=True
Else
ActiveWindow.SelectedSheets.PrintPreview
End If
End If
End Sub

I get an error "Subscript out of range" run-time error 9

What I am trying to do is as follows:-
The excle file has over 100 sheets, some of which are
hidden. Sheets are broken down into 10 sections, but
there may only be 4 sheets in a given section that
require printing.

I use a table to list which sheets are hidden, or not.

To get the value for prange - as in the code, I use a
lookup on this table.

The data in prange is currently
"CAT0", "00", "01", "06", "07", "08", "09"

as you can see, sheets "02", "03", "04", "05" are hidden,
and do not require printing.

When I replace prange in the vb code, with this value,
the macro works just fine.

What can I do ?

 

[acw]

Gary

Change the line
prange = Sheets("macroinputs").Range("O8").Value to
prange = evaluate("={" & Range("O8").Value & "}")

and the line
Sheets(Array(prange)).Select to
sheets(prange).select

I think there is a more elegant way to do this but not
sure what it is.

Tony

 

[Gary Burke]

Thanks for the input, but alas, now get an error

Run-time error 13 - Type mismatch on

prange = evaluate("={" & Range("O8").Value & "}")

I have not used the evaluate function before - sorry !!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

? Range("O8")
"CAT0", "00", "01", "06", "07", "08", "09"
prange = Evaluate("{" & Range("O8").Value & "}")
? prange(1)
CAT0
? prange(7)
09
Worksheets(prange).Select
? activewindow.SelectedSheets.Count
7

works fine for me. Demod from the immediate window.

Regards,
Tom Ogilvy

 

[Gary Burke]

Hi Tom,

Many thanks for your patience, however, same error is still being
returned. (I had tried including the sheet name before responding last
time).

I have also tried with data as follows:-

a) "DEPT TTL", "CAT0", "10", "11", "13", "16"

b) DEPT TTL", "CAT0", "10", "11", "13", "16 - note the missing starting
and ending "

Any other thoughts ?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

I showed what was in my Range("O8") and that it worked for me.

I don't know what else to tell you. That is about the only way to do it if
you have this string in a single cell.

sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Array(sStr)

does not work

sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate( "{" & sStr & "}")

Here, run this code:

Sub Tester4()
Dim sStr As String
Dim prange As Variant
Dim i As Long
sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate("{" & sStr & "}")
Debug.Print sStr
For i = LBound(prange) To UBound(prange)
Debug.Print i, prange(i), Worksheets(prange(i)).Name
Next
End Sub

this produced:
"CAT0", "00", "01", "07", "08"
1 CAT0 CAT0
2 00 00
3 01 01
4 07 07
5 08 08

If you don't know how to use the immediate window:

Sub Tester4()
Dim sStr As String
Dim prange As Variant
Dim i As Long
sStr = """CAT0"", ""00"", ""01"", ""07"", ""08"""
prange = Evaluate("{" & sStr & "}")
MsgBox sStr
For i = LBound(prange) To UBound(prange)
msgbox i & " " & prange(i) & " " & Worksheets(prange(i)).Name
Next
End Sub

You don't have to use ""00"" in the cell - that is just for building a
string directly.
Regards,
Tom Ogilvy

 

[Gary Burke]

Hi Tom,

Ran the demod as suggested, and DONT get the type mismatch error,
however, when the macro runs, I do get the error, although the macro
does comile OK.

Could there be something missing from my preferences ?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Gary Burke]

found the problem

dim prange as string

should be

dim prange as variant

Thanks for all the help

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!