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
...
Next
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
there.
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)
Next
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.)