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?
--
Carlee

On Apr 26, 9:38 pm, Carlee wrote:
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?
--
Carlee

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

On Apr 26, 9:38 pm, Carlee wrote:
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?
--
Carlee

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