How to fix: 'subscript out of range', Run time error '9'

Question

New to VBA, is issue seems to be the following line,

Set NoMonths = Worksheets("Sheet4").Range("C13")

Unsure why as in cell c13 on sheet4 there contains a number value for example 5.

Can someone help please.

Full code:

Sub DefferedRev()

    Set NoMonths = Worksheets("Sheet4").Range("C13")
    Dim i As Integer
    Dim q As Integer


    If Worksheets("Sheet13").Range("B40") = Worksheets("Sheet13").Range("C82") Then
        For i = 5 To 7
            Cells(48, i).Value = 0
        Next i
    Else
        For i = 5 To 7
            q = (i Mod NoMonths) - 5
            Dim VectorToSum() As String
            ReDim VectorToSum(1 To q)
            For w = 1 To q
                VectorToSum(w) = (Worksheets("Sheet13").Cells(38, i).Value * Worksheets("Sheet13").Cells(7, i).Value) / (NoMonths * NoMonths - w)
            Next w
            ValueIn = Application.WorksheetFunction.Sum(VectorToSum)

            Cells(48, i).Value = ValueIn
        Next i
    End If

End Sub

Answer 1

Subscript out of range

means that the Worksheets() property cannot find a worksheet named Sheet4.

1. Make sure the sheet name has no additional spaces before, after or between Sheet4. In case of any doubt rename it to ensure correct naming of the sheet.

2. In case of working with multiple workbooks (or in general following a good practice) specify in which workbook your worksheet is by using:

   Set NoMonths = ThisWorkbook.Worksheets("Sheet4").Range("C13") 'ThisWorkbook is the workbook this code is in
   

---

Additionally I recommend to use Option Explicit and declare all your variables proplery before you use them. For Example:

Dim NoMonths As Range
Set NoMonths = ThisWorkbook.Worksheets("Sheet4").Range("C13") 

Further if you use Cells(48, i).Value = 0 you don't define in which workbook this cell is. So Excel makes a guess and it might fail. Always specify a workbook/worksheet for all Range, Cells, Rows, Columns objects.

Thisworkbook.Worksheets("Sheet13").Cells(48, i).Value = 0

Better approach is to set the worksheets to a variable to make your code short and clean and re-use that variable.

Dim ws As Worksheet
Set ws = Thisworkbook.Worksheets("Sheet13")

ws.Cells(48, i).Value = 0

Finally I highly recommend to use meaningful variable names and worksheet names. Sheet13 and Sheet4 are pretty bad choices for names. Choosing a good name should be the very fist thing you do. Because if you do that last you will have to change it everywhere in your code.

---

So in the end your code should be something like this:

Option Explicit

Public Sub DefferedRev()
    Dim NoMonths As Range
    Set NoMonths = ThisWorkbook.Worksheets("Sheet4").Range("C13")

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet13")


    If ws.Range("B40").Value = ws.Range("C82").Value Then
        'For i = 5 To 7
        '    ws.Cells(48, i).Value = 0
        'Next i

        'this loop can be written at once (faster!)
        'replace it with the following line
        ws.Range(ws.Cells(48, 5), ws.Cells(48, 7)).Value = 0
    Else
        Dim i As Long
        For i = 5 To 7
            Dim q As Long
            q = (i Mod NoMonths) - 5

            Dim VectorToSum() As String
            ReDim VectorToSum(1 To q)

            Dim w As Long
            For w = 1 To q
                VectorToSum(w) = (ws.Cells(38, i).Value * ws.Cells(7, i).Value) / (NoMonths * NoMonths - w)
            Next w

            ws.Cells(48, i).Value = Application.WorksheetFunction.Sum(VectorToSum)
        Next i
    End If
End Sub