Re: Runtime error 9 - subscript out of range error

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Hello All

I hope you can help on this please

Re: Runtime error 9 - subscript out of range error on the code below that is bold​

[Lastrow = Sheets("data").Cells(Rows.Count, "I").End(xlUp).Row
For i = 2 To Lastrow

If Cells(i, "J").Value = "D" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "K").End(xlUp).Row + 1, "K")

If Cells(i, "J").Value = "C" Then Cells(i, 1).Resize(, 8).Copy Sheets(Cells(i, "I").Value).Cells(Sheets(Cells(i, "I").Value).Cells(Rows.Count, "B").End(xlUp).Row + 1, "B")

Next]

 

[Leith Ross]

Re: Runtime error 9 - subscript out of range error

Hello VbaHell,

From what I understand what you want to do, I believe this should it.

Sub Populate()


    Dim DstWks  As Worksheet
    Dim i       As Long
    Dim Lastrow As Long
    Dim SrcWks  As Worksheet
    Dim vArray  As Variant
    Dim WksName As Variant
                    
        vArray = Array("August 18", "July 18", "June 18", "May 18", "April 18", "March 18", "February 18", "January 18", "December 17", _
                        "November 17", "October 17", "September 17", "August 17", "July 17")
                        
        Set SrcWks = Sheets("data")
            Lastrow = SrcWks.Cells(Rows.Count, "B").End(xlUp).Row
        
            SrcWks.Range("K1").FormulaR1C1 = "Text Date"
            SrcWks.Range("K2:K" & Lastrow).FormulaR1C1 = "=TEXT(RC[-2],""mmmm yy"")"
    
            SrcWks.Range("K2:K" & Lastrow).Value = SrcWks.Columns("I2:I" & Lastrow).Value
            
        For Each WksName In vArray
            Set DstWks = Worksheets(WksName)
            For i = 2 To Lastrow
                If SrcWks.Cells(i, "J").Value = "D" Then SrcWks.Cells(i, 1).Resize(, 8).Copy DstWks.Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
                If SrcWks.Cells(i, "J").Value = "C" Then SrcWks.Cells(i, 1).Resize(, 8).Copy DstWks.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            Next i
        Next WksName
     
        Call Formats
    
End Sub

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Close this thread, pointless

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Hi Leath Thank you very much for helping me on this, I will check this out at the weekend as I am away at the moment
I must admit I was loosing the will to live with this post.

 

[Leith Ross]

Re: Runtime error 9 - subscript out of range error

Hello VbaHell,

The macro assumes all of the sheets in the array exist in the workbook. If that is not the case you can use this amended version to handle the errors...

Macro with Error Handling

Sub Populate()


    Dim DstWks  As Worksheet
    Dim i       As Long
    Dim Lastrow As Long
    Dim SrcWks  As Worksheet
    Dim vArray  As Variant
    Dim WksName As Variant
                    
        vArray = Array("August 18", "July 18", "June 18", "May 18", "April 18", "March 18", "February 18", "January 18", "December 17", _
                        "November 17", "October 17", "September 17", "August 17", "July 17")
                        
        Set SrcWks = Sheets("data")
            Lastrow = SrcWks.Cells(Rows.Count, "B").End(xlUp).Row
        
            SrcWks.Range("K1").FormulaR1C1 = "Text Date"
            SrcWks.Range("K2:K" & Lastrow).FormulaR1C1 = "=TEXT(RC[-2],""mmmm yy"")"
    
            SrcWks.Range("K2:K" & Lastrow).Value = SrcWks.Columns("I2:I" & Lastrow).Value
            
        For Each WksName In vArray
            On Error Resume Next
                Set DstWks = Worksheets(WksName)
                If Not Err Then
                    For i = 2 To Lastrow
                        If SrcWks.Cells(i, "J").Value = "D" Then SrcWks.Cells(i, 1).Resize(, 8).Copy DstWks.Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
                        If SrcWks.Cells(i, "J").Value = "C" Then SrcWks.Cells(i, 1).Resize(, 8).Copy DstWks.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
                    Next i
                Else
                    MsgBox "The Worksheet """ & WksName & """ was Not Found.", vbExclamation
                End If
            On Error GoTo 0
        Next WksName
     
        Call Formats
    
End Sub

 

[VbaHell]

Re: Runtime error 9 - subscript out of range error

Cheers Leith, appreciated