Let's break down the issues in your code and fix that frustrating 1004 error step by step:

Key Issues Causing the Error #

1. Reversed Find Logic: Your Else branch has the logic backwards—when you find a matching cell (otk exists), you just reset J4 to 0 without updating the data. When no match is found, you try to paste to D.Range(otk) (which is Nothing at that point), triggering the 1004 error.
2. Mismatched Variable Declarations: You declared DATA and Input worksheet variables but used D and I instead. This works but hurts readability and maintainability.
3. Redundant Loop: The While L=1 loop serves no purpose since you immediately set L=0 to exit it. We can remove this entirely.
4. Inconsistent Paste Target: When updating, you need to paste the copied range to the row of the matched cell, not just the cell itself (since you're copying multiple columns).

Corrected Code #

Sub add_data()
    Dim wsData As Worksheet
    Dim wsInput As Worksheet
    Dim matchedCell As Range
    Dim lastRow As Long
    
    ' Assign worksheet references clearly
    Set wsData = ThisWorkbook.Sheets("DATA")
    Set wsInput = ThisWorkbook.Sheets("Input")
    
    ' Check update flag
    If wsInput.Range("J4").Value = 0 Then
        ' Add new row: Get last row from B1 (as per your original logic)
        lastRow = wsData.Range("B1").Value
        ' Direct value assignment (faster than copy/paste)
        wsData.Range("A" & lastRow).Resize(1, wsInput.Range("K4:UX4").Columns.Count).Value = wsInput.Range("K4:UX4").Value
    Else
        ' Update existing row: Find match in DATA's A column
        With wsData.Range("A1:A10000")
            Set matchedCell = .Find(What:=wsInput.Range("K4").Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not matchedCell Is Nothing Then
                ' Paste values to the matched row, starting at column A
                wsData.Range(matchedCell.Address).Resize(1, wsInput.Range("K4:UX4").Columns.Count).Value = wsInput.Range("K4:UX4").Value
                ' Reset update flag after successful update
                wsInput.Range("J4").Value = 0
            Else
                ' Optional: Handle case where no match is found
                MsgBox "No matching record found for " & wsInput.Range("K4").Value, vbExclamation
            End If
        End With
    End If
End Sub

What We Changed #

• Clearer Variable Names: Used wsData and wsInput instead of D/I to make the code easier to follow.
• Direct Value Assignment: Replaced Copy/PasteSpecial with direct value transfer—this is faster, avoids clipboard conflicts, and reduces the chance of runtime errors.
• Fixed Find Logic: Now when a match is found, we paste the data to the correct row and reset the update flag. If no match is found, we show a helpful message (you can adjust this behavior if needed).
• Resized Target Range: Used Resize to ensure the target range matches the size of the copied range, preventing mismatched range errors.
• Removed Redundant Loop: The loop was unnecessary, so we simplified the code to use a straightforward conditional check.

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