Search for duplicates in a column

[Guest]

Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?

 

[matt]

Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?

Why not sort the data, and then compare one cell with the next?

For example

Dim currCell
Dim nextCell
Dim counter
Dim a

counter = activesheet.range("b2").currentregion.rows.count 'or use
usedrange...

for a = 1 to counter
currCell = range("b" & a).value
nextCell = range("b" & a).offset(1,0).value
if currCell = nextCell then
msgbox prompt:="You have a duplicate." & vbcr _
& "The value is " & currCell & vbcr _
& "The location is " a & " & " & a + 1, buttons:=vbOkOnly
end if
next

Matt

 

[matt]

Hi,

I am trying to figure out how to search through column B of Sheet1. If
there are duplicates, display a message stating such, with the duplicate
value contained in the msgbox.

ideas?

Why not sort the data and then compare one cell with the other?

for example (not tested)

dim currCell
dim nextCell
dim counter
dim a

counter = activesheet.range("b1").currentregion.rows.count 'or use
usedrange...

for a = 1 to counter
currCell = range("b" & a).value
nextCell = range("b" & a).offset(1,0).value

if currCell = nextCell then
msgbox prompt:="You have a duplicate." & vbcr _
& "The value is " & currCell & vbcr _
& "The location is " & a & " & " a + 1,
buttons:=vbokonly
end if
next

matt