Excel VBA：用户表单数据写入表格时VLOOKUP错误2042的处理及数据校验问题求助

Let's break down the issues in your code and fix them step by step:

Key Issues in Your Original Code #

• Typo Error: You wrote Filed1 instead of Field1 in the second VLOOKUP line—this is a direct cause of unexpected behavior since the variable name is misspelled.
• Incorrect VLOOKUP Usage: Your check2 and check3 use VLOOKUP wrong. VLOOKUP searches for the lookup value in the first column of your range. When you pass Field1 as the lookup value, it's searching for that value in Column A (Fullname column), not checking if the existing Fullname's Field1 matches the form input.
• Error Handling Misstep: The Err.Number check won't catch VLOOKUP's #N/A error (error 2042), because this is a worksheet function error returned as a Variant value, not a VBA runtime error. You need to use IsError() instead.
• Premature Row Addition: You're adding a new list row before validating if the data already exists—this means even when you choose to overwrite, you'll end up with a new row instead of updating the existing one.
• Duplicate Textbox Reference: You assigned Field2 to TextBox2 (same as Field1) instead of a separate input like TextBox3.

Corrected VBA Code #

Sub SubmitEmployeeData()
    Dim Fullname As String
    Dim Field1 As String
    Dim Field2 As String
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim existingRow As ListRow
    Dim lookupResult As Variant
    Dim yesNo As Integer
    
    ' Initialize variables with form inputs
    Fullname = AddEmployee_UF.TextBox1.Value
    Field1 = AddEmployee_UF.TextBox2.Value
    Field2 = AddEmployee_UF.TextBox3.Value ' Fixed: Assigned to correct textbox
    Set ws = ThisWorkbook.Worksheets("Zaměstnanci")
    Set tbl = ws.ListObjects("tblZaměstnanci")
    
    ' Step 1: Check if Fullname already exists in the table
    lookupResult = Application.VLookup(Fullname, tbl.DataBodyRange, 1, False)
    
    If IsError(lookupResult) Then
        ' No existing entry: Add new row
        Set existingRow = tbl.ListRows.Add
        With existingRow
            .Range(1) = Fullname
            .Range(2) = Field1
            .Range(3) = Field2
        End With
        MsgBox "Data written successfully!", vbInformation, "Success"
    Else
        ' Entry exists: Get the exact matching row
        Set existingRow = tbl.ListRows(Application.Match(Fullname, tbl.ListColumns(1).DataBodyRange, 0))
        
        ' Check if existing data is identical to form input
        If existingRow.Range(2).Value = Field1 And existingRow.Range(3).Value = Field2 Then
            MsgBox "Data is already identical to existing entry.", vbInformation, "No Changes Needed"
        Else
            ' Prompt user to overwrite
            yesNo = MsgBox("Data already exists. Do you want to overwrite?", vbQuestion + vbYesNo, "Overwrite?")
            If yesNo = vbYes Then
                With existingRow
                    .Range(2) = Field1
                    .Range(3) = Field2
                End With
                MsgBox "Data overwritten successfully!", vbInformation, "Overwrite Complete"
            End If
        End If
    End If
    
    ' Reset form fields for next entry (optional)
    AddEmployee_UF.TextBox1.Value = ""
    AddEmployee_UF.TextBox2.Value = ""
    AddEmployee_UF.TextBox3.Value = ""
    ' Unload AddEmployee_UF ' Uncomment this line if you want to close the form instead of resetting
End Sub

What's Improved? #

• Fixed Typos & References: Corrected the Filed1 typo and assigned Field2 to the correct textbox.
• Valid Lookup Logic: Now we first check if the Fullname exists using VLOOKUP on the table's first column. If it exists, we use Match to target the exact row, then compare existing Field1/Field2 values to the form input.
• Proper Error Handling: Used IsError() to catch VLOOKUP's #N/A error (error 2042) when no match is found.
• Correct Row Handling: Only adds a new row when there's no existing entry; for overwrites, we modify the existing row instead of creating a duplicate.
• Better User Feedback: Added specific messages for identical data, successful writes, and overwrites to guide the user clearly.

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