Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicates (RP)
Hi. The code below is compliments of Bob Phillips. It scans the contents
of column D, and finds duplicates. If the code finds them, a message box displaying where the dulicates are found is displayed. This code works great if there are duplicates found. If there are no duplicates, I get an Invalid procedure call or argument error, with the line sCells = Left(sCells, Len(sCells) - 1) highlighted by the debugger. Any ideas how to fix? Thank you!! Sub Dups() Dim iLastRow As Long Dim i As Long Dim sCells As String Dim rng As Range iLastRow = Cells(1499, "B").End(xlUp).Row 'Cells(Rows.Count, "B") Set rng = Range("D1:D" & iLastRow) For i = 1 To iLastRow If Application.CountIf(rng, Cells(i, "D")) 1 Then sCells = sCells & Cells(i, "D").Address(False, False) & "," End If Next i sCells = Left(sCells, Len(sCells) - 1) If Not IsEmpty(sCells) Then MsgBox "Duplicates found in " & vbCrLf & sCells Else MainUpdate End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicates (RP)
sCells will never be empty since it is initialized as a null string. But
you can do Sub Dups() Dim iLastRow As Long Dim i As Long Dim sCells As String Dim rng As Range iLastRow = Cells(1499, "B").End(xlUp).Row 'Cells(Rows.Count, "B") Set rng = Range("D1:D" & iLastRow) For i = 1 To iLastRow If Application.CountIf(rng, Cells(i, "D")) 1 Then sCells = sCells & Cells(i, "D").Address(False, False) & "," End If Next i If sCells < "" Then sCells = Left(sCells, Len(sCells) - 1) MsgBox "Duplicates found in " & vbCrLf & sCells Else MainUpdate End If End Sub -- Regards, Tom Ogilvy "Steph" wrote in message ... Hi. The code below is compliments of Bob Phillips. It scans the contents of column D, and finds duplicates. If the code finds them, a message box displaying where the dulicates are found is displayed. This code works great if there are duplicates found. If there are no duplicates, I get an Invalid procedure call or argument error, with the line sCells = Left(sCells, Len(sCells) - 1) highlighted by the debugger. Any ideas how to fix? Thank you!! Sub Dups() Dim iLastRow As Long Dim i As Long Dim sCells As String Dim rng As Range iLastRow = Cells(1499, "B").End(xlUp).Row 'Cells(Rows.Count, "B") Set rng = Range("D1:D" & iLastRow) For i = 1 To iLastRow If Application.CountIf(rng, Cells(i, "D")) 1 Then sCells = sCells & Cells(i, "D").Address(False, False) & "," End If Next i sCells = Left(sCells, Len(sCells) - 1) If Not IsEmpty(sCells) Then MsgBox "Duplicates found in " & vbCrLf & sCells Else MainUpdate End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for Duplicates (RP)
Thanks Tom!
"Tom Ogilvy" wrote in message ... sCells will never be empty since it is initialized as a null string. But you can do Sub Dups() Dim iLastRow As Long Dim i As Long Dim sCells As String Dim rng As Range iLastRow = Cells(1499, "B").End(xlUp).Row 'Cells(Rows.Count, "B") Set rng = Range("D1:D" & iLastRow) For i = 1 To iLastRow If Application.CountIf(rng, Cells(i, "D")) 1 Then sCells = sCells & Cells(i, "D").Address(False, False) & "," End If Next i If sCells < "" Then sCells = Left(sCells, Len(sCells) - 1) MsgBox "Duplicates found in " & vbCrLf & sCells Else MainUpdate End If End Sub -- Regards, Tom Ogilvy "Steph" wrote in message ... Hi. The code below is compliments of Bob Phillips. It scans the contents of column D, and finds duplicates. If the code finds them, a message box displaying where the dulicates are found is displayed. This code works great if there are duplicates found. If there are no duplicates, I get an Invalid procedure call or argument error, with the line sCells = Left(sCells, Len(sCells) - 1) highlighted by the debugger. Any ideas how to fix? Thank you!! Sub Dups() Dim iLastRow As Long Dim i As Long Dim sCells As String Dim rng As Range iLastRow = Cells(1499, "B").End(xlUp).Row 'Cells(Rows.Count, "B") Set rng = Range("D1:D" & iLastRow) For i = 1 To iLastRow If Application.CountIf(rng, Cells(i, "D")) 1 Then sCells = sCells & Cells(i, "D").Address(False, False) & "," End If Next i sCells = Left(sCells, Len(sCells) - 1) If Not IsEmpty(sCells) Then MsgBox "Duplicates found in " & vbCrLf & sCells Else MainUpdate End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for duplicates? | Excel Worksheet Functions | |||
check for duplicates | Excel Worksheet Functions | |||
Check for Duplicates | Excel Worksheet Functions | |||
Check for Duplicates | Excel Worksheet Functions | |||
Check for duplicates | Excel Worksheet Functions |