Find Duplicate Data

[bboysen]

I am trying to find duplicate data in a column with user input. I have tried the countif in vba it works if i tell it the range i want like (If Application.Worksheet.CountIf(Range("M2:M" & D), Range("M" & D)) > 1 Then), but if i use a name range from of the user selection i cant get it to work. I am new to this still and trying to learn but hit road blocks like this all of the time.

Here is the code:

Sub CheckDup()
'
' CheckDup Macro
'

'

Dim D As Long
Dim finalrow As Long
Dim rRange As Range

finalrow = ActiveSheet.UsedRange.Rows.Count
Count = 1
For D = 2 To finalrow
If Cells(D, 13).Value = 0 Then
Else
On Error Resume Next

Application.DisplayAlerts = False

Set rRange = Application.InputBox(Prompt:= _
"Please select a Column with your Mouse to be Checked.", _
Title:="SPECIFY RANGE", Type:=2)

On Error GoTo 0

If Application.Worksheet.CountIf(Range(rRange & D), Range(rRange & D)) > 1 Then
Cells(D, rRange).Resize(1, 1).Interior.ColorIndex = 5
End If
End If
Next D


End Sub

I am trying to do this because the data moves from column to column depending on the user.

Any help would be great

 

[gsbelbin]

I haven't had time to look in great depth but my first thoughts are:-

You need to change the input box return type to 8. You have it set to 2 which will return a string.

To identify the column of rRange you need to use rRange.Column which will return the number of the column.

If you're going to use CountIf you need to precede it with WorksheetFunction not Worksheet. You'll also need to look at the syntax for this function ie CountIf(Range of cells you want to count, criteria which you will use for counting). Have a look in Help.

What exactly is the end product that you are looking for? Do you just want to highlight the cells that have duplicates?

 

[bboysen]

Yes all i am trying to do is Highlight the Duplicates in a Column based on a user pick if what column they would like to check.

I made a few little tweaks to it and now it will run through the VBA with out an error but it will not highlight it.

I think by Chaging to the Worksheetfunction. made a difference.

So this is what i have now:

Dim D As Long
Dim finalrow As Long
Dim rRange As Range


On Error Resume Next

Application.DisplayAlerts = False

Set rRange = Application.InputBox(Prompt:= _
"Please select a Column with your Mouse to be Checked.", _
Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0
finalrow = ActiveSheet.UsedRange.Rows.Count
Count = 1
For D = 2 To finalrow
If Cells(D, 13).Value = 0 Then
Else
If Application.WorksheetFunction.CountIf(Range(rRange & D), Range(rRange & D)) > 1 Then
Cells.Resize(1, 1).Interior.ColorIndex = 5
End If
End If
Next D

 

[xenou]

This looks funny (I'm surprised no error):

Application.WorksheetFunction.CountIf([COLOR="DarkRed"]Range(rRange & D[/COLOR]), Range([COLOR="DarkRed"]rRange & D[/COLOR])) > 1

Should it be (?) (I think gsbelbin was hinting at the same):

If Application.WorksheetFunction.CountIf(rRange,Cells(D,rRange.Column)) > 1 Then

 

[bboysen]

That Worked Thanks for the Help Guys....