Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
I have another question. There are the "'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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
Any way I took out all the "s 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to check for duplicates Newbie pls help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) | |||
VBA Newbie: Help with Do Loop code | Excel Programming | |||
Loop macro for a Newbie | Excel Programming | |||
newbie: stop a FOR NEXT loop | Excel Programming | |||
Newbie. Please help me find a loop solution | Excel Programming |