Consulting

Results 1 to 4 of 4

Thread: Subscript error when finding next blank row in added worksheet

  1. #1
    VBAX Regular
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    15
    Location

    Subscript error when finding next blank row in added worksheet

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Mar 2020
    Location
    Ammon
    Posts
    15
    Location
    Yep! That did it! Thanks again, Paul!


    - Ben

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •