I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much

Without knowing anything more about your numbers, I would go with this.
Change A1:A100 to reflect the cells you want to check

Sub ABC()
Dim rng as Range, cell as Range
Dim i as Long, c1 as Long
set rng = Range("A1:A100")
for each cell in rng("A1:A100")
v = split(cell.Text,",")
for i = lbound(v) to ubound(v)
c1 = application.countif(rng,"*" & v(i) &"*")
if c1 1 then ' it should be 1 to match itself
msgbox v(i) & " possible dups"
cell.Interior.ColorIndex = 3
end if
next
Next
End Sub

--
regards,
Tom Ogilvy


"Janis" wrote:

I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much

thanks, I WILL TRY IT, just seeing the structures help me get started. I
can probably tweak it to get it to work. Will this one take all the numbers
in the cell and check it against all the numbers in all the other cells
plural?
Regards,
Janis

"Tom Ogilvy" wrote:

Without knowing anything more about your numbers, I would go with this.
Change A1:A100 to reflect the cells you want to check

Sub ABC()
Dim rng as Range, cell as Range
Dim i as Long, c1 as Long
set rng = Range("A1:A100")
for each cell in rng("A1:A100")
v = split(cell.Text,",")
for i = lbound(v) to ubound(v)
c1 = application.countif(rng,"*" & v(i) &"*")
if c1 1 then ' it should be 1 to match itself
msgbox v(i) & " possible dups"
cell.Interior.ColorIndex = 3
end if
next
Next
End Sub

--
regards,
Tom Ogilvy


"Janis" wrote:

I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much

I have another question. There are the &quot's in my question email that I
did not put in.
Is that because I am on a mac? How do I get it to not put in the escape
characters?
Janis
"Tom Ogilvy" wrote:

Without knowing anything more about your numbers, I would go with this.
Change A1:A100 to reflect the cells you want to check

Sub ABC()
Dim rng as Range, cell as Range
Dim i as Long, c1 as Long
set rng = Range("A1:A100")
for each cell in rng("A1:A100")
v = split(cell.Text,",")
for i = lbound(v) to ubound(v)
c1 = application.countif(rng,"*" & v(i) &"*")
if c1 1 then ' it should be 1 to match itself
msgbox v(i) & " possible dups"
cell.Interior.ColorIndex = 3
end if
next
Next
End Sub

--
regards,
Tom Ogilvy


"Janis" wrote:

I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much

Any way I took out all the &quots and it compiles. I think it looks like the
comma in the cell split allows it to check for all the numbers in each cell
so it looks like it will work. Thanks again.

"Tom Ogilvy" wrote:

Without knowing anything more about your numbers, I would go with this.
Change A1:A100 to reflect the cells you want to check

Sub ABC()
Dim rng as Range, cell as Range
Dim i as Long, c1 as Long
set rng = Range("A1:A100")
for each cell in rng("A1:A100")
v = split(cell.Text,",")
for i = lbound(v) to ubound(v)
c1 = application.countif(rng,"*" & v(i) &"*")
if c1 1 then ' it should be 1 to match itself
msgbox v(i) & " possible dups"
cell.Interior.ColorIndex = 3
end if
next
Next
End Sub

--
regards,
Tom Ogilvy


"Janis" wrote:

I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much

Oh, I figured out you need the quotes. Thanks again.

"Tom Ogilvy" wrote:

Without knowing anything more about your numbers, I would go with this.
Change A1:A100 to reflect the cells you want to check

Sub ABC()
Dim rng as Range, cell as Range
Dim i as Long, c1 as Long
set rng = Range("A1:A100")
for each cell in rng("A1:A100")
v = split(cell.Text,",")
for i = lbound(v) to ubound(v)
c1 = application.countif(rng,"*" & v(i) &"*")
if c1 1 then ' it should be 1 to match itself
msgbox v(i) & " possible dups"
cell.Interior.ColorIndex = 3
end if
next
Next
End Sub

--
regards,
Tom Ogilvy


"Janis" wrote:

I have random program numbers in a cell in each row for example in R4,C4,
406518,417615, 42586. I need to run through all the rows with a loop to
check that there are no duplicate numbers in any of the other cells. I need
an inner and an outer loop. I have the outer loop I think. I need to
compare the first line cell contents and then run through every row to make
sure the first program numbers aren't in any other cells. Then I need to
proceed to the second line compare those numbers with every other line
through the end of the spread sheet ensuring that there are no duplicate
program numbers in the 2nd column.

Here is all I have so far:
Sub ckDups()
Range("R1C2").Select
Do While ActiveCell.Value < ""
???????????
ActiveCell.Offset(1, 0).Select
Loop
End Sub

thanks very much