如何通知用户他们已经创建了一个重复的记录，并询问他们是否想要覆盖?

Question

我有一个工作簿，并创建了命令按钮。我已经成功地创建了VBA的2个按钮，但我挣扎于一个重复的代码。我正在从工作表的特定单元格传输数据到标题为"Master"我的文件是从保存命令自动创建的，该命令将工作表自动保存为发票(c10)，然后将项目ref (a12)保存。我正试图将代码写入我现有的VBA，告诉我正在添加副本，并问我是否要覆盖它?

这是我已经走了多远，我花了几个小时在重复函数上，试图纠正它，但我一直摔倒。除了("C10")之外，行中允许所有其他重复。我只是不能让这一切为我而存在。请问有人能帮忙吗?

Dim Item As Range, DataEntry As Range
Dim Data() As Variant
Dim i As Integer
Dim DataRow As Long
Dim wsMaster As Worksheet

Set wsMaster = ThisWorkbook.Worksheets("Master")

Set DataEntry = ActiveSheet.Range("C10,A3,A12,E10,F33")

Dim assetIDRange, a As Range
For Each Item In DataEntry
i = i + 1
ReDim Preserve Data(1 To i)
Data(i) = Item.Value
Next

DataRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row + 1
wsMaster.Cells(DataRow, 1).Resize(1, DataEntry.Cells.Count).Value = Data
MsgBox "Record Added To Master", 48, "Record Added"
End Sub

Answer 1

试试这样:

Sub InsertUpdate()
Dim Item As Range, DataEntry As Range
Dim Data() As Variant
Dim i As Integer
Dim wsMaster As Worksheet
Dim m, c As Range, action

Set wsMaster = ThisWorkbook.Worksheets("Master")

Set DataEntry = ActiveSheet.Range("C10,A3,A12,E10,F33")

ReDim Data(1 To 1, 1 To DataEntry.Cells.Count)
For Each Item In DataEntry.Cells
i = i + 1
Data(1, i) = Item.Value
Next

'existing row with same C10 value?
m = Application.Match(Data(1, 1), wsMaster.Columns("a"), 0)

If Not IsError(m) Then 'found existing match?
If MsgBox("Replace existing entry for '" & Data(1, 1) & "' ?", vbQuestion + vbYesNo) = vbYes Then
Set c = wsMaster.Columns("a").Cells(m)
action = "Updated"
End If
Else
Set c = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Offset(1, 0)
action = "Added"
End If

If Not c Is Nothing Then
c.Resize(1, UBound(Data, 2)).Value = Data
MsgBox "Record " & action & " To Master", 48, "Record Added"
End If

End Sub