MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

ck for dupes in a split cell

  Asked By: Bara    Date: Sep 20    Category: MS Office    Views: 891

What I am trying to do is check all product numbers in a split cell (
commas as delimiters). It has to compare each prod number against all
other numbers to ensure no dupes. This has a compile error on the
split line.

Private Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = R1C2.Count.End(xlUp)
Set rng = Range("B1:RowCount")
For Each cell In rng
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
End Sub



3 Answers Found

Answer #1    Answered By: Adanalie Garcia     Answered On: Sep 20

The problem seems to be with the line

rowCount = R1C2.Count.End(xlUp)

I do not know what this is supposed to do but, whatever, it does not do it.


Set rng = Range("B1:RowCount")

Will not work because range  needs one or two cell  addresses and "RowCount" is
a long, i.e.a number  not an address.

Answer #2    Answered By: Ginger Snyder     Answered On: Sep 20

I don't get a compile  error on the split  line. In fact I don't get that
far. Looking at your code:

R1C2? Where does this come from? It's not a variable or a parameter. If
it is a named range, then it'll need to be used inside a Range() call. I
get a compile error  on this line. Can I assume you wanted to actually
address the cell  at Row 1, Column 2. I.e.

rowCount = Range("B1").Count.End(xlUp)

or rowCount = Cells(1, 2).Count.End(xlUp)

This also produces a compile error, this time on Count. The reason is that
Count returns a number, but End needs a range. Seeing your destination
variable is called rowCount, it's obviously looking for a Count. But the
Count from Range("B1") will always be 1, so you don't need to compute it. I
suspect that you want to develop a range  that extends from B1 down to the
last entry in B, then take the count  of all of that. Seeing you've used
xlUp, I assume you want to go to the bottom possible row and then search up
for the last used row:

rowCount = Range("B1", Range("B65536").End(xlUp)).Count

Next line  is

Set rng = Range("B1:RowCount")

This is an illegal string to give to Range, and it'll crash at run time.
You need to put an explicit B in and substitute in the bottom row number:

Set rng = Range("B1:B" & rowCount)

But ... you don't need that bit. You never use rowCount for anything else
and the result of your xlUp is to produce exactly the range you've just
generated. So

rowCount = Range("B1", Range("B65536").End(xlUp)).Count
Set rng = Range("B1:B" & rowCount)

becomes just

Set rng = Range("B1", Range("B65536").End(xlUp))

and you remove the Dim of rowCount.

Moving along:

For Each cell In rng

Your Next should be "Next Cell" to tell the compiler (and us) what it is
related to. It's optional, but (very) bad practice to leave it out. On the
other hand, I really like "For Each" and many people seem to forget it's

v = Split(cell.Text, ",")

produces a compile error because v is not defined. (You are using Option
Explicit I hope?) Seeing Split will redefine v just put in:

Dim v

to tell the compiler (and us) that you were using v deliberately and that it
wasn't a typo.

For i = LBound(v) To UBound(v)

Again, it should be "Next i".

c1 = Application.CountIf(rng, "*" & v(i) & "*")

Only you will know if this is working properly, because you have the details
about what's in your cells. However, a couple of points:

- You are splitting based on commas. Should you Trim v(i) just in case some
commas had spaces after them? (People are so used to typing a space after a
comma that they can slip through.)

- You are using a search that will match  just the character string in v(i).
Consider v(i) is part number  123. This will match items for part 1234,
4123, 123123123123, etc. Even if your part number starts with something
unique - e.g. P - you'd still find P123 matching P1234, P123123123, etc. If
your part numbers  are all the same length, then you won't have this problem.

Just one last point. Get into the habit right from the start of using
meaningful variable names, not "v" or "c1" or "rng". If you get into the
habit of using non-meaningful names, you'll never be able to debug anything
bigger than a few tens of lines, and people will blow raspberries at you if
you post such code to a list. (BTW I didn't include "cell" in that list.
Inside a short For loop, such as this one, "For Each cell In ..." actually
aids readability.)

Answer #3    Answered By: Jimmy Abp     Answered On: Sep 20

Thanks for explaining the option explicit, how to find
range  and how to name the variables. I can't afford
to make a mistake.

Didn't find what you were looking for? Find more on ck for dupes in a split cell Or get search suggestion and latest updates.