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

 

[Darren Smith]

It gets to the

For i = LBound(arr, 1) To UBound(arr, 1)

Then says Type mismatch any ideas why.
I`ve sent my updated workbook for you to look at

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

Thanks for your help so far

 

[Darren Smith]

Sorry, ignore the above.
Please can you help me to add ranges to the array?
Also, it needs to find the values in column C of the card worker sheet and use them to create the rows for the range in the destination sheet.

Otherwise, the code is fine.

 

[kanadaaa]

Also, it needs to find the values in column C of the card worker sheet and use them to create the rows for the range in the destination sheet.

If you're trying to get "descriptions" on Job Card Master, you can try something like this:

Sub GetColCArray()
    Dim descriptions() As String, fnd As Range, tempFnd As Range, i As Long
   
    With Worksheets("Job Card Master")
   
        Set fnd = .Range(Range("C12"), Range("C" & Rows.Count)).Find("*")
        Set tempFnd = fnd
        Do While Not fnd Is Nothing
            If fnd.MergeCells = False And fnd <> "Description" Then
                ReDim Preserve descriptions(i)
                descriptions(i) = fnd.Value
                i = i + 1
            End If
            Set fnd = .Range(Range("C12"), Range("C" & Rows.Count)).FindNext(fnd)
            If fnd.Address = tempFnd.Address Then Exit Do
        Loop
       
        Debug.Print Join(descriptions, vbCrLf)
        'Add action for "use them to create the rows for the range in the destination sheet"
       
    End With
End Sub

Maybe it'd best if you could keep me posted on this thread so that I can give you a hand when I have time to do so.

 

[Rick Rothstein]

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

From Columns 1, 3 and 11 on which sheet?

To Columns 1, 2 and 5 on which sheet?

 

[kanadaaa]

From Columns 1, 3 and 11 on which sheet?

To Columns 1, 2 and 5 on which sheet?

Probably from columns 1, 3 and 11 on "Job Card Master" and to Columns 1, 2 and 5 on "Check Sheet".

 

[Darren Smith]

If you're trying to get "descriptions" on Job Card Master, you can try something like this:

Sub GetColCArray()
    Dim descriptions() As String, fnd As Range, tempFnd As Range, i As Long
 
    With Worksheets("Job Card Master")
 
        Set fnd = .Range(Range("C12"), Range("C" & Rows.Count)).Find("*")
        Set tempFnd = fnd
        Do While Not fnd Is Nothing
            If fnd.MergeCells = False And fnd <> "Description" Then
                ReDim Preserve descriptions(i)
                descriptions(i) = fnd.Value
                i = i + 1
            End If
            Set fnd = .Range(Range("C12"), Range("C" & Rows.Count)).FindNext(fnd)
            If fnd.Address = tempFnd.Address Then Exit Do
        Loop
     
        Debug.Print Join(descriptions, vbCrLf)
        'Add action for "use them to create the rows for the range in the destination sheet"
     
    End With
End Sub

View attachment 36787

Maybe it'd best if you could keep me posted on this thread so that I can give you a hand when I have time to do so.

The code is very good but I need column A which is Item and column E which is Description and K which is Hrs Spent?
But I like your simple code and would like to use it if possible.