在 Excel 表中，用戶在 A 到 H 列中進行資料輸入
in col A - id
col B - Name
col C - DOB
col D - Mobile
col E - Email id
col F - Res-Address
col G - City
col H - State
在此資料中，用戶必須在 col E - 電子郵件 ID 的整行上找到并洗掉重復項，如果用戶輸入具有相同電子郵件 ID 的資料，然后已經存在的資料應在整行上用顏色突出顯示，并將顯示 Msgbox You Enter Duplicate Data已存在 如果要洗掉重復單擊，請參見行已突出顯示（是）
在這里，我找到了一個 vba 代碼并應用于我的作業表，以在用戶輸入任何重復資料時自動避免重復
我在這段代碼中得到（編譯錯誤）
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim cell As String
Dim lCount As Long
Set rRange = Range("E1", Range("E" & Rows.Count).End(xlUp))
lCount = rRange.Rows.Count
For lCount = lCount To 1 Step -1
With rRange.Cells(lCount, 1)
If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
.EntireRow.Interior.ColorIndex = 27
MsgBox "You Enter Duplicate Data is Already Exist See Row has Highlighted " & vbNewLine & " If you want to Delete Duplicate Click (Yes) vbYesNo vbDefaultButton2 = vbYes Then"
.EntireRow.Delete
MsgBox "Duplicate Entry Deleted"
End If
End If
End With
Next lCount
End Sub
uj5u.com熱心網友回復：
首先，洗掉第二個End If。
其次，你必須Option Explicit在你的模塊頂部。如果您希望保留它，那么您需要定義rRange.
如果您洗掉Option Explicit，它應該會消失。前者是最好的方法。
這編譯...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lCount As Long
Dim rRange As Range
Set rRange = Range("E1", Range("E" & Rows.Count).End(xlUp))
lCount = rRange.Rows.Count
For lCount = lCount To 1 Step -1
With rRange.Cells(lCount, 1)
If WorksheetFunction.CountIf(rRange, .Value) > 1 Then
.EntireRow.Interior.ColorIndex = 27
MsgBox "You Enter Duplicate Data is Already Exist See Row has Highlighted " & vbNewLine & " If you want to Delete Duplicate Click (Yes) vbYesNo vbDefaultButton2 = vbYes Then"
.EntireRow.Delete
MsgBox "Duplicate Entry Deleted"
End If
End With
Next lCount
End Sub
...雖然不確定它是否符合您的要求。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/406755.html
標籤：