CODEWITHSUNDEEP
excelvba
Alessandro
Alessandro

Reputation: 23

ThisWorkbook.Sheets("Sheet1") generates Run-Time error '9' Subscript out of range

I am trying to build a userform to input a new row (first row after headers).

The userform contains 6 fields of which 4 are combobox (lists) and 2 text box.

When I run the code it gets stuck at: Set ssheet = ThisWorkbook.Sheets("Sheet1") 

Private Sub CommandButton1_Click()
    Dim ssheet As Worksheet

    Set ssheet = ThisWorkbook.Sheets("Sheet1")

    nr = ssheet.Cells(Rows.Count, 1).End(x1Up).Row + 1

    ssheet.Cells(nr, 1) = Me.tbNAME
    ssheet.Cells(nr, 2) = Me.cmbStatus
    ssheet.Cells(nr, 3) = Me.cmbFunds
    ssheet.Cells(nr, 4) = Me.cmbDD
    ssheet.Cells(nr, 7) = Me.cmbDistributor
    ssheet.Cells(nr, 8) = Me.tbComments

End Sub

Private Sub UserForm_Initialize()
    Me.tbDate = Date

    For Each blah In [StatusList]
        Me.cmbStatus.AddItem blah
    Next blah

    For Each blah In [FundsList]
        Me.cmbFunds.AddItem blah
    Next blah

    For Each blah In [DDList]
        Me.cmbDD.AddItem blah
    Next blah

    For Each blah In [DistributorList]
        Me.cmbDistributor.AddItem blah
    Next blah

End Sub

Upvotes: 1

Views: 1825

Answers (1)

Kubie
Kubie

Reputation: 1571

When using the line:

Set ssheet = ThisWorkbook.Sheets("Sheet1")

Make sure "Sheet1" is actually the name of your sheet on your Workbook

Per your comment you can use:

Set ssheet = ThisWorkbook.Sheets(1) or Set ssheet = ThisWorkbook.Sheets("Master Log")

And for the following line:

nr = ssheet.Cells(ssheet.Rows.Count, 1).End(xlUp).Row + 1

Upvotes: 1

Related Questions