Excel VBA用户表单填充表格H列公式自动填充及报错修正

Let's break down the issues in your code and fix them step by step to get your H column formula working correctly and eliminate the type mismatch error.

Key Issues in the Original Code #

• Duplicate Worksheet Assignment: You overwrote the wks variable (set to "Tracker") with "Formulas" immediately after, meaning all your data was being written to the wrong sheet.
• Uninitialized ListIndex: The ListIndex variable was never assigned a value, so trying to use it to reference a cell in column AD caused an invalid row number. Plus, you tried assigning a Range object directly to a variable without .Value, triggering the type mismatch.
• Wrong Return Value in AppendToColumn: The function returned the last filled row before adding new data, but you need the row where the new data was inserted to build your formula correctly.
• Misaligned Formula Rows: Your formula should reference the same row in Tracker's G column and Formulas' AD column, since Formulas!AD is tied to Tracker's L column logic for that row.

Corrected VBA Code #

' Define the DataType enum at the top of your module (if not already present)
Enum DataType
    DataValue
    FormulaValue
End Enum

Private Sub CommandButton1_Click()
    Dim wks As Worksheet, wkf As Worksheet
    Dim newRow As Long ' Store the row number where new data is inserted
    
    ' Correctly assign separate variables for each sheet
    Set wks = ThisWorkbook.Sheets("Tracker")
    Set wkf = ThisWorkbook.Sheets("Formulas")
    
    ' Append data to Tracker sheet, capture the new row number each time
    newRow = AppendToColumn(wks, "A", JobID.Text)
    AppendToColumn(wks, "B", CoordName.Text)
    AppendToColumn(wks, "C", PlannerName.Text)
    AppendToColumn(wks, "D", Surveyor.Text)
    AppendToColumn(wks, "E", RRGuy.Text)
    AppendToColumn(wks, "F", DateBox.Text)
    newRow = AppendToColumn(wks, "G", TimeBox.Text) ' Update to the row where G column data was added
    AppendToColumn(wks, "I", AddressBox.Text)
    AppendToColumn(wks, "J", CityBox.Text)
    AppendToColumn(wks, "K", PostcodeBox.Text)
    AppendToColumn(wks, "L", THPBox.Text)
    AppendToColumn(wks, "M", JointBox.Text)
    
    ' Add the formula to H column using the new row number
    AppendToColumn wks, "H", "=G" & newRow & " + Formulas!AD" & newRow, FormulaValue
    
    MsgBox "All data has been added successfully"
End Sub

Private Function AppendToColumn(ByRef ws As Worksheet, _
                               ByVal column As Variant, _
                               ByVal value As Variant, _
                               Optional ByVal kindOfValue As DataType = DataValue) As Long
    '--- Copies the given value/formula to the first empty cell in the specified column.
    '    Returns the index of the row where the new data was inserted.
    Dim colIndex As Long
    Dim lastRow As Long
    Dim firstEmptyRow As Long
    
    With ws
        ' Convert column reference to numeric index
        colIndex = IIf(IsNumeric(column), column, .Cells(1, column).Column)
        lastRow = .Cells(.Rows.Count, colIndex).End(xlUp).Row
        
        ' Determine first empty row (handle empty column case)
        firstEmptyRow = IIf(IsEmpty(.Cells(lastRow, colIndex)), 1, lastRow + 1)
        
        ' Write value or formula to the cell
        Select Case kindOfValue
            Case DataValue
                .Cells(firstEmptyRow, colIndex).Value = value
            Case FormulaValue
                .Cells(firstEmptyRow, colIndex).Formula = value
        End Select
    End With
    
    ' Return the row where we just added data (critical for building the formula)
    AppendToColumn = firstEmptyRow
End Function

What Changed & Why #

1. Separated Worksheet Variables: We now use wks for Tracker and wkf for Formulas, so data goes to the correct sheet.
2. Captured New Row Number: The AppendToColumn function now returns the row where new data was inserted (firstEmptyRow), so we can use this exact row number for the H column formula.
3. Fixed Formula Logic: The H column formula now references the same row in both G and Formulas!AD, ensuring it's tied to the correct row's data (matches your requirement that Formulas!AD is determined by Tracker's L column for that row).
4. Eliminated Type Mismatch: No more uninitialized variables or invalid Range assignments — we're using the direct row number from the function return to build the formula.

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