Re: Runtime error 9 - subscript out of range error

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,206
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]
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Re: Runtime error 9 - subscript out of range error

Do you have a sheet name with whatever is in cells(i,"I") when you get the error?
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,206
Re: Runtime error 9 - subscript out of range error

Hi Fluff

This is my full code

Code:
Sub Populate()
Application.ScreenUpdating = False
'On Error GoTo M
Dim i As Long
Dim ans As Long
Sheets(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")).Select
    Sheets("August 18").Activate
    Range("B9:I80").Select
    Selection.ClearContents
    
    Range("K9:R80").Select
    Selection.ClearContents
    
Sheets("data").Activate
Dim Lastrow As Long
Lastrow = Sheets("data").Cells(Rows.Count, "B").End(xlUp).Row
Range("K1").FormulaR1C1 = "Text Date"
    With Range("K2:K" & Lastrow)
        .FormulaR1C1 = "=TEXT(RC[-2],""mmmm yy"")"
        '.Value = .Value
    End With
    
 Columns("K:K").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Lastrow = Sheets("data").Cells(Rows.Count, "B").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
     
 Call Formats
    
Application.ScreenUpdating = True
Exit Sub
'M:
'MsgBox "That sheet name does not exist or you had some other sort of problem"
Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Re: Runtime error 9 - subscript out of range error

When posting code please use code tags, the # icon in the reply window.
Also you haven't answered my question
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,206

ADVERTISEMENT

Re: Runtime error 9 - subscript out of range error

Sorry for not posting the code correctly, I will insert the icon next time

"
Do you have a sheet name with whatever is in cells(i,"I") when you get the error? " - With the error occurring when I hover over (i'"I") I get nothing at all, if this is what your asking

I do get I = 222 which is the last row, That's the (i, (Little i)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Re: Runtime error 9 - subscript out of range error

If cells(i,"I") is empty that would explain the error as you cannot have a sheet without a name
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,206

ADVERTISEMENT

Re: Runtime error 9 - subscript out of range error

Can you point me in the right direction
How do I can or add to the code, it populate's the Sheets(array (All the tabs with the months)

Should the array be referenced to "I" some how
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Re: Runtime error 9 - subscript out of range error

What do you want to happen if the sheet does not exist?
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,206
Re: Runtime error 9 - subscript out of range error

It copies from the sheet named "Data" and inputs into all the tabs in the sheet array named below

Code:
[LEFT][COLOR=#333333][FONT=monospace]Sheets(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")).Select
    Sheets("August 18").Activate[/FONT][/COLOR][/LEFT]

If the value in the Data sheet column "J" ="D" then copy the row to all the tabs in the array from column "K"
If the value in the Data sheet column "J" ="C" then copy the row to all the tabs in the array from column "B"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Re: Runtime error 9 - subscript out of range error

You're code does not copy the data to all the tabs. It copies the data to a sheet with the same name as the cells in col I.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,938
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top