Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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 -----Original Message----- 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 ? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
prange = Evaluate( "{" & Sheets("macroinputs").Range("O8").Value & "}")
Should work. Regards, Tom Ogilvy Gary B wrote in message ... 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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
? 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 wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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 wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ARRAYS
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avg Arrays | Excel Worksheet Functions | |||
Use of IF with arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions |