- Sub SaveData()
- Dim Ar, Br, Cr(), i%, n%, k%, m%, D
- n = Cells(Rows.Count, 3).End(xlUp).Row
- If n = 1 Then
- MsgBox "没有要保存的数据!"
- Exit Sub
- End If
- Ar = Range("c2:m" & n)
- Set D = CreateObject("scripting.dictionary")
- With Sheets("历史明细")
- n = .Cells(Rows.Count, 2).End(xlUp).Row
- If n > 1 Then
- Br = .Range("b2:f" & n)
- For k = 1 To n - 1
- D(Br(k, 1) & Br(k, 2) & Br(k, 3) & Br(k, 4) & Br(k, 5)) = ""
- Next
- End If
- End With
- ReDim Cr(1 To UBound(Ar), 1 To 6)
- For k = 1 To UBound(Ar)
- If D.exists(Ar(k, 3) & Ar(k, 2) & Ar(k, 4) & Ar(k, 1) & Ar(k, 11)) Then
- If MsgBox("第" & k + 1 & "行数据已经存在,是否继续保存?", vbYesNo, "请确认") = vbNo Then GoTo Newline
- End If
- m = m + 1
- Cr(m, 1) = Date: Cr(m, 2) = Ar(k, 3): Cr(m, 3) = Ar(k, 2): Cr(m, 4) = Ar(k, 4): Cr(m, 5) = Ar(k, 1): Cr(m, 6) = Ar(k, 11)
- Newline:
- Next
- If m > 0 Then
- Sheets("历史明细").Range("a" & n + 1).Resize(m, 6) = Cr
- MsgBox "数据保存完毕!"
- End If
- End Sub
复制代码 |