The array says subscript out of range

[Darren Smith]

This array says subscript out of range. But the array is within the range of the worksheet?
If fails on this part of the code?

If Len(Arr(i, 8)) = 1 Then

wsCSheet.Range("A12:G39").Offset(Row) = Arr(i, 8)
Row = Row + 1

The whole code below.

Private Sub Fill_Details_Click()

TurnOff

Dim wsCSheet As Worksheet, JCM As Worksheet
Dim LastRow As Long, i As Long
Dim Arr As Variant

    Set JCM = ThisWorkbook.Worksheets("Job Card Master")
    LastRow = JCM.Cells(Rows.Count, 1).End(xlUp).Row
    Set wsCSheet = ThisWorkbook.Worksheets("Check Sheet")

wsCSheet.Range("A12:G39").ClearContents

Arr = JCM.Range("A13:K61")

For i = LBound(Arr) To UBound(Arr)

If Len(Arr(i, 8)) = 1 Then

wsCSheet.Range("A12:G39").Offset(Row) = Arr(i, 8)
Row = Row + 1

End If
 
 Next i

TurnOn

End Sub

 

[kanadaaa]

There's no declaration of the variable Row and no value substituted for this variable. What is it for?
Technically, you don't need to "declare" it so this might not be the cause of the error though.
I just wonder why you need the variable LastRow, which isn't used anywhere in the code.

 

[Darren Smith]

There's no declaration of the variable Row and no value substituted for this variable. What is it for?
Technically, you don't need to "declare" it so this might not be the cause of the error though.
I just wonder why you need the variable LastRow, which isn't used anywhere in the code.

My plan was to take selected columns from a worksheet then add them to a different worksheet.
Sorry, what do you mean by you don`t need to declare it?
As to the rows, I have done what you said and it still does not work.
Sorry, my code now has the last row in it I have just altered it.
The altered part is below

With wsCSheet

.Range("A12:G39").ClearContents

Arr = JCM.Range("A13:K" & LastRow)

Row = 12

For i = LBound(Arr) To UBound(Arr)

If Len(Arr(i, 1)) = 11 Then

.Range("A12").Offset(Row) = Arr(i, 1, 3, 11)
Row = Row + 1

End If

Next i

End With

 

[kanadaaa]

Sorry but I'm not sure what's causing the error.
Can you maybe share your workbook via https://easyupload.io/

 

[kanadaaa]

Wait, if the code that errored out just looks like the one in #3, the error might be because of this line:

.Range("A12").Offset(Row) = Arr(i, 1, 3, 11)

I see no reason that the array has to be four-dimensional.
Isn't it just that the code errors out with "Subscript out of range" because there's no four-dimensional array in your code?

 

[Darren Smith]

I need to find columns 1, 3 & 11 then transfer them to a different sheet Col, 1, 2 & 5.
Also taking away any blank rows in the column when it fills into the next sheet.
If that makes sense?
The workbook is below

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[RoryA]

Your array is 2D so you can't specify 4 dimension values like this: Arr(i, 1, 3, 11). You would need something like:

.Range("A12").Offset(Row).Resize(, 3).Value = Application.Index(Arr, i,Array(1, 3, 11))

 

[kanadaaa]

I need to find columns 1, 3 & 11 then transfer them to a different sheet Col, 1, 2 & 5.

That's just not how the array works, and it can be done without creating an array (although it CAN be done with an array).
For example:

Sub CopyColumns()
    Dim JCM As Worksheet, i As Long, lr As Long, j As Long, k As Long
    Set JCM = Worksheets("Job Card Master")
    With Worksheets("Check Sheet")
        Application.ScreenUpdating = False 

        For i = 1 To 11 Step 2
            If i = 1 Or i = 3 Or i = 11 Then
                j = Choose(i, 1, , 2, , , , , , , , 5)
                For k = 13 To JCM.Cells(Rows.Count, i).End(xlUp).Row
                    If JCM.Cells(k, i) <> "" Then
                        lr = .Cells(Rows.Count, j).End(xlUp).Row + 1
                        .Cells(lr, j) = JCM.Cells(k, i)
                    End If
                Next k
            End If
        Next i
     
        Application.ScreenUpdating = True
    End With
 
End Sub

 

[Darren Smith]

I find arrays works faster I just need to know how to add the array to column 1, 2 & 5 on the destination worksheet.
But thanks for your help

 

[kanadaaa]

Sub CopyColumns_Array()
    Dim arr As Variant, lr As Long, JCM As Worksheet, csCol As Long, i As Long, csRow As Long, jcmCol As Long
    
    Set JCM = Worksheets("Job Card Master")
    lr = JCM.Range("A:K").Find("*", , , , xlByRows, xlPrevious).Row
    arr = JCM.Range("A13:K" & lr)
        
    Application.ScreenUpdating = False
    For csCol = 1 To 5
        If csCol = 1 Or csCol = 2 Or csCol = 5 Then
            jcmCol = Choose(csCol, 1, 3, , , 11)
            csRow = 12
            For i = LBound(arr, 1) To UBound(arr, 1)
                If arr(i, jcmCol) <> "" Then
                    Worksheets("Check Sheet").Cells(csRow, csCol) = arr(i, jcmCol)
                    csRow = csRow + 1
                End If
            Next i
        End If
    Next csCol
    Application.ScreenUpdating = True
    
End Sub