Subscript error when finding next blank row in added worksheet

[bostenson]

I want to find the next blank row in a newly added worksheet but I have a subscript out of range error in line WSNr = Worksheets(i).Cells(Rows.Count, "A").End(xlUp).Row + 1. This is the complete code:


Private Sub cmdSaveGL_Click()

Dim WSNr As Long

Dim a As Integer
Dim i As Long
a = ThisWorkbook.Worksheets.Count

' Find next blank row


WSNr = Worksheets(i).Cells(Rows.Count, "A").End(xlUp).Row + 1

' Add Expense data to Registers


If Me.cmbGLCategory.value = "Expense" Then
For i = 1 To a
If Worksheets(i).Name = cmbGLCashAcct.value Then
Worksheets(i).Range("A" & WSNr).value = Me.txbGLName.value
End If
Next
End If

End Sub




What am I missing in my code in order to find the blank row in the new sheet? Thanks in advance.


- Ben

[Paul_Hossler]

You're using "i" before you assign it any value



WSNr = Worksheets(i).Cells(Rows.Count, "A").End(xlUp).Row + 1



I'm guessing that the troublesome line is not where you want it??



Private Sub cmdSaveGL_Click()

Dim WSNr As Long

Dim a As Integer
Dim i As Long
a = ThisWorkbook.Worksheets.Count


' Add Expense data to Registers


If Me.cmbGLCategory.value = "Expense" Then
For i = 1 To a

' Find next blank row
WSNr = Worksheets(i).Cells(Rows.Count, "A").End(xlUp).Row + 1


If Worksheets(i).Name = cmbGLCashAcct.value Then Worksheets(i).Range("A" & WSNr).value = Me.txbGLName.value End If Next End If End Sub

[bostenson]

Yep! That did it! Thanks again, Paul!


- Ben

[SamT]

Private Sub cmdSaveGL_Click()

'Test for Sheet exists
'Uses True on Error
On Error Resume next
If Not Sheets(Me.cmbGLCashAcct.value).Name = Me.cmbGLCashAcct.value Then
Err.Clear
MsgBox "sheet " & Me.cmbGLCashAcct & " Does not exist. Fix it!"
End 'Stop all code
End If

' Add Expense data to Registers
Sheets(cmbGLCashAcct.value).Cells(Rows.Count, "A").Offset(1) = Me.txbGLName