Excel VBA嵌套循环问题求助：按数据类型选模板并填充文本框

Let's fix up your VBA code step by step to get it working exactly how you need it. I noticed a few key issues in your original script that were throwing off the logic, so here's the corrected version plus a breakdown of what changed:

Corrected VBA Code #

Sub Template()
    Dim wsSample As Worksheet
    Dim c As Range
    Dim targetTemplate As String
    Dim newWs As Worksheet
    Dim name1Val As String, name2Val As String
    
    ' Set a clear reference to your sample sheet (avoids bugs from active sheet reliance)
    Set wsSample = ThisWorkbook.Sheets("Sample")
    
    ' Loop through each non-empty cell in column I starting at row 3
    For Each c In wsSample.Range("I3", wsSample.Cells(wsSample.Rows.Count, "I").End(xlUp))
        ' Skip empty or whitespace-only cells in column I
        If Trim(c.Value) = "" Then
            Continue For ' Cleanly skip to the next row iteration
        End If
        
        ' Pick the correct template based on Type value
        If c.Value = "DEN" Then
            targetTemplate = "D-Temp"
        Else
            targetTemplate = "M-Temp"
        End If
        
        ' Grab Name1 and Name2 from the same row (adjust offsets if your columns differ!)
        ' Based on your sample, assuming Name1 = column J (I+1), Name2 = column K (I+2)
        name1Val = c.Offset(, 1).Value
        name2Val = c.Offset(, 2).Value
        
        ' Optional: Skip rows where both names are empty
        If Trim(name1Val) = "" And Trim(name2Val) = "" Then
            Continue For
        End If
        
        ' Copy the selected template to the end of the workbook
        ThisWorkbook.Sheets(targetTemplate).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Set newWs = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        
        ' Populate the textboxes, with error handling for typos in textbox names
        On Error Resume Next
        newWs.Shapes("Textbox 1").TextFrame.Characters.Text = name1Val
        newWs.Shapes("Textbox 2").TextFrame.Characters.Text = name2Val
        On Error GoTo 0 ' Reset error handling to default
    Next c
End Sub

Key Fixes & Improvements #

• Explicit Worksheet References: We use Set wsSample to avoid bugs caused by relying on the active sheet, which can change unexpectedly
• Proper Empty Cell Handling: Your original empty row check had invalid syntax—Trim(c.Value) = "" catches both blank and whitespace-only cells, and Continue For skrows cleanly
• Row-Matched Data Logic: Instead of nesting a separate loop over column P, we use Offset to pull Name1 and Name2 directly from the same row as the Type value in column I (adjust the offset numbers if your Name columns are in different positions!)
• Dynamic Template Selection: We use the targetTemplate variable to copy the correct template ("D-Temp" or "M-Temp") instead of hardcoding one
• Textbox Error Handling: The On Error Resume Next block prevents the macro from breaking if textbox names have minor typos (like "TextBox 1" vs "Textbox 1")

How It Works With Your Sample Data #

For your sample rows:

The macro will:

1. Skip the blank row in column I
2. For the "DEN" row: copy the "D-Temp" sheet, fill Textbox 1 with "Suyi" and Textbox 2 with "Nick"
3. For the "PX" row: copy the "M-Temp" sheet, fill Textbox 1 with "Mac" and Textbox 2 with "Cruise"

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