VBA - The right spot to update last row within a For loop

Question

When I tried to output the value to the worksheet from userform's combobox and textbox, I found that the second or below rows of the combobox and textbox could not update to the worksheet. I realise the problem is caused by missing the line for updating the last row after each time copy the value from the combobox and textbox within the userform.

Here is the outfit of the userform:

And a part of my codes:

Dim Nws As Worksheet, tb1 As ListObject
Dim range_output As Range
Dim n As Long
Dim lRow As Long
Dim Ctrl As Control
Dim strW As String
Set Nws = ThisWorkbook.Worksheets("Fill")
Set range_output = Nws.Range("A19:E30")
Set tb1 = Nws.ListObjects("Output")

With tb1.ListColumns(2).Range
    lRow = .Find(What:="*", After:=.Cells(1), SearchDirection:=xlPrevious).Row + 1
End With


For Each Ctrl In UserForm1.Controls
    If TypeOf Ctrl Is MSForms.ComboBox Then
        If Ctrl.Value <> "" Then
            Select Case Int(Replace(Ctrl.Name, "ComboBox", ""))
            Case 3 To 12, 23 To 32, 53 to 62   '3&4&5: Location
                Range("A1").Cells(lRow, 1).Value = Ctrl.Value
            Case 13 To 22            '3: Work Type
                Range("A1").Cells(lRow, 2).Value = Ctrl.Value
            Case 33 To 42            '4: Work Type
                n = Int(Replace(Ctrl.Name, "ComboBox", "")) + 10
                strW = "4-" & Ctrl.Value & Controls("ComboBox" & n).Value
                Range("A1").Cells(lRow, 2).Value = strW
            Case 63 To 72            '5: Work Type
                n = Int(Replace(Ctrl.Name, "ComboBox", "")) + 10
                strW = "5-" & Ctrl.Value & Controls("ComboBox" & n).Value
                Range("A1").Cells(lRow, 2).Value = strW
            End Select
        End If
    
    ElseIf TypeOf Ctrl Is MSForms.TextBox Then
        If Ctrl.Value <> "" Then
            Select Case Int(Replace(Ctrl.Name, "TextBox", ""))
            Case 16 To 25, 36 To 45, 56 To 65   'Staff
                Range("A1").Cells(lRow, 3).Value = Ctrl.Value
            Case 26 To 35, 46 To 55, 66 To 75   'Number
                Range("A1").Cells(lRow, 4).Value = Ctrl.Value
            End Select
        End If
    End If
Next Ctrl

I want to find the right spot to place the line of lRow = lRow + 1 . However, it is difficult since I want the same row of the combobox and textbox on the userform output to the same row of the worksheet, ie. Combobox 3, Combobox 13, Textbox 16 and Textbox 26 output to Cells(lRow, 1 to 4).

I have thought about adding another variable i. Add i = i + 0.25 each time Ctrl have run, and lRow = lRow + worksheetfunction.rounddown(i , 0). However, the page 3 have 4 items: Location, Work type, Staff and Number, but the page 4 and 5 have 5 items: Location, Work type, Facility, Staff, Number. I cannot make it work.

Thanks.

Answer 1

This doesn't directly answer your question but consider filling an 3-dimensional array first with all the values from the form. It should be easier then to create the rows to be added to the table. Run this demo in a new workbook, no need to have a form. The result shows object names but can easily be changed to use UserForm1.Controls(name).Value

Option Explicit

Sub demo()

   'create 3D array (page, row, column)
   Dim ar(1 To 5), i As Long, pg
   For i = 1 To 5
      ReDim pg(1 To 10, 1 To 4)
      ar(i) = pg
   Next
   
   ' fill array from form
   FillArrays ar
   
   ' write to sheet
   Range("A1:D10") = ar(3)
   Range("A12:D21") = ar(4)
   Range("A23:D32") = ar(5)
   MsgBox "done"

End Sub

Sub FillArrays(ByRef ar)
   
    Dim r As Long, p As Long, c As Long
    Dim i As Long, s As String
    
    ' comboboxes
    For i = 3 To 72
       
        ' page
        If i >= 53 Then
           p = 5
        Else
           p = 3 + Int((i - 3) / 20)
        End If
        ' row
        r = 1 + (i - 3) Mod 10
        'column
        Select Case i
            Case 3 To 12, 23 To 32, 53 To 62 ' locations
                c = 1
            Case 13 To 22, 33 To 42, 63 To 72 ' work
                c = 2
            Case Else
                c = 0
        End Select
        
        If c > 0 Then
            s = "ComboBox" & i
            ar(p)(r, c) = s 'use UserForm1.Controls(s) to get value
        End If
    Next
        
    ' textboxes
    For i = 16 To 75
        p = 3 + Int((i - 16) / 20)
        r = 1 + (i - 16) Mod 10
        Select Case i
            Case 16 To 25, 36 To 45, 56 To 65   'Staff
                c = 3
            Case 26 To 35, 46 To 55, 66 To 75   'Number
                c = 4
        End Select
        s = "TextBox" & i
        ar(p)(r, c) = s 'use UserForm1.Controls(s) to get value
     Next

End Sub