VBA UserForm职位与培训关联功能异常技术求助

Hey there, let's sort out your UserForm output issue. Looking at your code, the main problem is how you're handling the training content loop—right now it's dumping all training entries into the same column/row and messing up the row numbering, which is why your output isn't matching what you expect.

What's Wrong with the Original Code? #

• Your inner loop For Each i In ws_Merge.Range("Merge_Training") iterates all training entries in the named range, not just the ones linked to the selected job title.
• You're overwriting the same cell (Cells(lRow,7)) with each training entry, then incrementing lRow—this leaves your name/job title only on the first row, and subsequent rows have no name/job data.

Corrected Code #

Here's the revised version that will output each linked training entry on its own row, with the name and job title repeated for each training item:

' Submits userform into table
Private Sub CommandButton1_Click() ' Make sure this matches your button's name
    Dim lRow As Long
    Dim ws As Worksheet
    Dim ws_Merge As Worksheet
    Dim titleCell As Range
    Dim trainingCell As Range
    Dim trainingRange As Range
    
    Set ws = Worksheets("DATA")
    Set ws_Merge = Worksheets("MERGE_DF")
    
    ' Find first empty row in DATA sheet
    lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    ' Verify data entered
    If Trim(Me.TextFirst.Value) = "" Then
        Me.TextFirst.SetFocus
        MsgBox "You Forgot the First Name"
        Exit Sub
    End If
    If Trim(Me.TextLast.Value) = "" Then
        Me.TextLast.SetFocus
        MsgBox "You Forgot the Last Name"
        Exit Sub
    End If
    If Trim(Me.CmboJob.Value) = "" Then
        Me.CmboJob.SetFocus
        MsgBox "You Forgot the Job Title"
        Exit Sub
    End If
    
    ' Loop through job titles to find matches
    For Each titleCell In ws_Merge.Range("Merge_Title")
        If titleCell.Value = Me.CmboJob.Value Then
            ' Get the training range linked to this job title (assuming training is in the same row, adjacent columns)
            ' Adjust the column offset (Offset(0,1)) if your training is in a different column relative to Merge_Title
            Set trainingRange = titleCell.Offset(0, 1).Resize(1, titleCell.End(xlToRight).Column - titleCell.Column)
            
            ' Loop through each training item for this job
            For Each trainingCell In trainingRange
                If Trim(trainingCell.Value) <> "" Then ' Skip empty training cells
                    With ws
                        .Cells(lRow, 1).Value = Me.TextLast.Value
                        .Cells(lRow, 2).Value = Me.TextFirst.Value
                        .Cells(lRow, 4).Value = Me.CmboJob.Value
                        .Cells(lRow, 7).Value = trainingCell.Value
                    End With
                    lRow = lRow + 1 ' Move to next row for the next training item
                End If
            Next trainingCell
            Exit For ' Exit once we find the matching job title (no need to check others)
        End If
    Next titleCell
    
    ' Reset userform
    Me.TextLast.Value = ""
    Me.TextFirst.Value = ""
    Me.CmboJob.Value = ""
    Me.CmboJob.SetFocus
End Sub

Key Changes Explained #

1. Targeted Training Range: Instead of looping all training entries, we get the specific training cells linked to the selected job title using Offset and Resize—adjust the column offset (Offset(0,1)) if your training data is in a different column relative to Merge_Title.
2. Per-Training Row Output: For each valid training item, we write the full name, job title, and training content to a new row, then increment lRow to move to the next line.
3. Exit After Match: Added Exit For once we find the matching job title to avoid unnecessary loops through other titles.
4. Empty Training Check: Added a check to skip empty cells in the training range, so you don't get blank rows in your output.

Quick Note #

Make sure your Merge_Title and Merge_Training ranges are structured correctly—this code assumes each job title in Merge_Title has its associated training items in the same row, to the right of the title. If your training is in a different layout (e.g., vertical list linked by job title), we can adjust the range selection further.

内容的提问来源于stack exchange，提问作者MrOtter