Checking for duplicates: VBA

[Jim]

Hi

Does anyone know a neat VBA way of checking for duplicates in a column. I
use OFFSET to get to a particular cell but then need to check whether the
one (or more) below it contain identical data (numeric)...

This is a scoring system and I'm trying to check for ties. I OFFSET to 7th
place which shows 7 in the cell but then need to check the next cell down to
see if that is 7 (and maybe the next one too).

Hope that makes sense. Any suggestions gratefully received!

Jim

 

[Guest]

I use the COUNTIF worksheet function - e.g.
CountTies = WorksheetFunction.Countif(YourCell.EntireColumn,YourCell.Value)
If this comes out >1, then there are that many "tied" scores for that value.

 

[Bob Phillips]

With ActiveCell
If .Offset(7,0).Value = .Offset(8,0).Value Then
Msgbox "same"
End If
End With


With ActiveCell
If .Offset(7,0).Value = .Offset(8,0).Value And _
.Offset(7,0).Value = .Offset(9,0).Value Then
Msgbox "same"
End If
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

Jim - Here is a macro I use to run down a column and check for duplicate
numbers. If a set of duplicates are found, the first one is changed to
"AAA" and the second is colored red. A formula is then inserted at the
bottom to count how many "AAA" cells - or how many duplicates - were found.
Righ now, it's hard-coded to count down Col. C and put the formula at the
bottom of Col. D.

HTH
Ed

Sub FindDups()
'
' FindDups Macro
Dim FirstItem As String, SecondItem As String
Dim Offsetcount As Long

' Select column
Range("C1").Select
ScreenUpdating = False

' Run down column and compare TIR numbers
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1

' If the numbers are the same
Do While ActiveCell <> ""
If FirstItem = SecondItem Then

' Turns the second one red
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
' Replaces first cell value with AAA
ActiveCell.FormulaR1C1 = "AAA"
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True

'Puts formula to count AAA values in D column
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C:C,AAA)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""AAA"")"
ActiveCell.Select

Range("C65536").End(xlUp).Offset(1, 1).Select

End Sub

 

[Jim]

Many thanks for the quick replies. I think Bob's or Ed's suggestions will
bear fruit. Thanks again.