PDA

View Full Version : Problem with creating Array



wannabburly
11-06-2015, 11:59 AM
I'm trying to create an array that will select sheets for printing to PDF, but keep getting the subscript out of range error. Below is the code I found that I'm trying to adapt. I feel like there is something very simple that I'm missing here...


Sub pdfSheets2()


Dim wb As Workbook
Dim SheetsArray() As Variant
Dim JobNumbers As Range
Dim fName As String
Dim fPath As String
Dim n As Integer
Dim i As Integer

fPath = '
fName = '

Set wb = ThisWorkbook

Set JobNumbers = Worksheets("Input").Range("A3", Range("A3").End(xlDown))
n = JobNumbers.Rows.Count

ReDim SheetsArray(i)
For i = 1 To n
SheetsArray(i) = JobNumbers.Cells(i, 1).Value2 'This line is highlighted when I debug.
Next i

wb.Sheets(SheetsArray).Select


ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fPath & fName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

jolivanes
11-06-2015, 04:57 PM
Is this what you have in mind?

Sub Try_This_Sheets()
Dim a, lr As Long
lr = Sheets("Input").Cells(Rows.Count, 1).End(xlUp).Row
a = WorksheetFunction.Transpose(Sheets("Input").Range("A3:A" & lr).Value)
Sheets(a).Select


'Do your printing here '<-----

End Sub

Paul_Hossler
11-06-2015, 05:20 PM
I'd try



ReDim SheetsArray(1 to n)


As it is, I think since I=0, you only have a one element array SheetsArray(0)

SamT
11-07-2015, 07:38 AM
Option Base 1

Sub ...........
'
'
ReDim SheetsArray(n)
For i = 1 to n

Kenneth Hobs
11-07-2015, 08:41 AM
Both methods should work for you.

A 3rd method is to use the Replace option for a Range's Select. Select the first to Replace as True and the others as False.

For your range selection method, it assumes that there are at least two sheet names. In some cases, one has to check if the worksheet exists before adding a value for sheet selection.

Since I tested the first suggestion, here it is:

Sub pdfSheets2()
Dim SheetsArray() As Variant
Dim fName As String
Dim fPath As String

fPath = "c:\temp\"
fName = "pdfSheets2.pdf"

SheetsArray() = WorksheetFunction.Transpose(Worksheets("Input").Range("A3", _
Worksheets("Input").Range("A3").End(xlDown)))
Worksheets(SheetsArray).Select

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fPath & fName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

wannabburly
11-08-2015, 08:09 AM
Thank you all for the responses.

I made the changes each of you suggested, but I still get the "Subscript out of range" error at the line where the array is selected.

SamT
11-08-2015, 08:24 AM
Watch the values of X


ReDim SheetsArray(i)
Dim X
X = i
X = JobNumbers.Cells(i).Value2 'Column reference is superfluous. JobNumbers is a single Column.
For i = 1 To n
SheetsArray(i) = JobNumbers.Cells(i).Value2
Next i

I usually set a Breakpoint at the first X, then hover the mouse over X while pressing F8 to step thru the code.If I like both values, then I press F5 to run the loop back to the Breakpoint again.

Paul_Hossler
11-08-2015, 08:27 AM
1. Post your code

2. Is it possible that there is a typo in the list of sheets in Range(A3:An) such A3="MySheet" but the actual sheet name is "My Sheet"?

Kenneth Hobs
11-08-2015, 08:43 AM
You have more wrong than just the redimension and index. To use something like your approach, it might be:

Sub pdfSheets3()
Dim wb As Workbook
Dim SheetsArray() As Variant
Dim JobNumbers As Range
Dim fName As String
Dim fPath As String
Dim n As Integer
Dim i As Integer

fPath = "c:\temp\"
fName = "pdfSheets3.pdf"

Set wb = ThisWorkbook

Set JobNumbers = Worksheets("Input").Range("A3", _
Worksheets("Input").Range("A3").End(xlDown))
n = JobNumbers.Cells.Count

ReDim SheetsArray(1 To n)
For i = 1 To n
SheetsArray(i) = JobNumbers(i).Value2 'This line is highlighted when I debug.
Next i

wb.Sheets(SheetsArray).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fPath & fName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

wannabburly
11-08-2015, 08:59 AM
Ok, this may be a stupid question, but after...

Set JobNumbers = Worksheets("Input").Range("A3", Range("A3").End(xlDown))

Shouldn't...

JobNumbers.Cells(i, 1).Value2
Return the data in cell A3? Where i starts at 1, A3 is Cells(1,1) in the JobNumbers range, right?

When I step through the code like SamT suggested, the first X found is the data in A2 (the title of the column "Job No.").

Kenneth Hobs
11-08-2015, 09:23 AM
Either use two worksheet references as I have shown or don't use any if active sheet is the one with the range.

Yes, that is easily tested.

Sub ken()
Dim JobNumbers As Range
Set JobNumbers = Worksheets("Input").Range("A3", _
Worksheets("Input").Range("A3").End(xlDown))
MsgBox JobNumbers.Cells(1, "A").Address
MsgBox JobNumbers(1).Address
End Sub

snb
11-08-2015, 09:35 AM
or

Sub M_snb()
sn=sheet("input").columns(3).specialcells(2)

for j=3 to ubound(sn)
msgbox sn(j,1)
next
End Sub

It all depends on the structure of the worksheet you are not showing.

SamT
11-08-2015, 09:44 AM
i = zero
When I step through the code like SamT suggested, the first X found is the data in A2 (the title of the column "Job No.").

When a range starts below Row 1, the zeroth Cell is just above the first Cell in the Range.

Kenneth's "ReDim SheetsArray(1 To n)", sets the starting index of the array to 1, the same as using "Option Base 1" at the top of the code page does.