VBA日志添加宏优化：新增Logs工作表E列重复值检查需求

现有AddLogs宏可正常运行，需新增以下功能：

• 检测Logs工作表E列（控制编号列）是否存在待添加的重复值
• 若存在重复，跳过该条记录的完整添加操作
• 将重复情况记录至Logs工作表

修改后的完整代码 #

Sub AddLogs()
'Add to ControlSheet
Dim nextrow As Long
Dim shLogs As String
Dim WSDATA As Worksheet
Dim LastRow As Long
Dim scode As String
Dim newControlNum As String ' 存储待添加的控制编号

Application.ScreenUpdating = False

Set WSDATA = ThisWorkbook.Sheets("RFP")
LastRow = WSDATA.Range("A" & Rows.Count).End(xlUp).Row
shLogs = "Logs" ' 提前赋值，避免循环内重复定义

For i = 4 To LastRow
    scode = WSDATA.Range("A" & i).Value
    If scode <> "" Then
        nextrow = ThisWorkbook.Sheets(shLogs).Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' 生成待添加的控制编号
        newControlNum = "FSS-2024-" & Format(WSDATA.Range("B" & i).Value, "00000")
        
        ' 检查Logs表E列是否存在重复的控制编号
        On Error Resume Next ' 捕获Match未找到的错误
        Dim matchRow As Long
        matchRow = WorksheetFunction.Match(newControlNum, ThisWorkbook.Sheets(shLogs).Range("E:E"), 0)
        On Error GoTo 0
        
        If matchRow > 0 Then
            ' 存在重复，仅记录基础信息与提示
            ThisWorkbook.Sheets(shLogs).Range("A" & nextrow).Value = nextrow - 1
            ThisWorkbook.Sheets(shLogs).Range("B" & nextrow).Value = WSDATA.Range("C16").Value
            ThisWorkbook.Sheets(shLogs).Range("D" & nextrow).Value = Format(WSDATA.Range("B1").Value, "MMMM dd, yyyy")
            ThisWorkbook.Sheets(shLogs).Range("E" & nextrow).Value = newControlNum
            ThisWorkbook.Sheets(shLogs).Range("K" & nextrow).Value = "【重复提示】该控制编号已存在，未执行完整添加"
        Else
            ' 无重复，正常写入所有字段
            ThisWorkbook.Sheets(shLogs).Range("A" & nextrow).Value = nextrow - 1
            'Prepared
            ThisWorkbook.Sheets(shLogs).Range("B" & nextrow).Value = WSDATA.Range("C16").Value
            'DATE
            ThisWorkbook.Sheets(shLogs).Range("D" & nextrow).Value = Format(WSDATA.Range("B1").Value, "MMMM dd, yyyy")
            'CONTROL NUMBER
            ThisWorkbook.Sheets(shLogs).Range("E" & nextrow).Value = newControlNum
            'PAYEE
            ThisWorkbook.Sheets(shLogs).Range("G" & nextrow).Value = UCase(WSDATA.Range("H" & i).Value)
            'BANK
            ThisWorkbook.Sheets(shLogs).Range("H" & nextrow).Value = WSDATA.Range("I" & i).Value
            'ACCOUNT NUMBER
            ThisWorkbook.Sheets(shLogs).Range("I" & nextrow).Value = WSDATA.Range("J" & i).Value
            'AMOUNT
            ThisWorkbook.Sheets(shLogs).Range("J" & nextrow).Value = WSDATA.Range("D" & i).Value
            'PARTICULARS
            ThisWorkbook.Sheets(shLogs).Range("K" & nextrow).Value = WSDATA.Range("K" & i).Value
            'CORPORATION
            ThisWorkbook.Sheets(shLogs).Range("L" & nextrow).Value = WSDATA.Range("L" & i).Value
        End If
    End If
Next i

Application.ScreenUpdating = True
End Sub

核心修改说明 #

• 提前生成待添加的控制编号并存储为变量，避免重复计算
• 利用WorksheetFunction.Match快速检索E列重复值，比循环查找效率更高
• 增加条件分支：重复时仅记录基础信息与提示，不执行完整字段写入；无重复时正常写入所有数据
• 将Application.ScreenUpdating = True移至循环外，减少界面刷新次数，提升宏运行效率

内容的提问来源于stack exchange，提问作者Domingo Ballesil Jr