I am assuming that column H contains entries with a empty cells every so
often:
a,b,a,b, <blank>, a,a,a, <blank>
You want to count the number of unique items in each block by replacing the
blank with a formula.
I have assumed there are only single blanks, this lets me simplify the
SUMPRODUCT; you can change it if needed
The statement Cells(j, "H").Interior.Color = 65535 was used to help me
debug the code; remove it if you wish
Sub CountUniques()
Dim sh As Worksheet, c As Range
'lastrow = sh.Cells(Rows.Count, 8).End(xlUp).Row
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row + 1
toprow = 1
For j = 1 To lastrow
If Cells(j, "H") = "" Then
mycount = j - toprow
myrange = "R[-" & mycount & "]C:R[-1]C"
myeqn = "=sumproduct(1/countif(" & myrange & "," & myrange & "))"
Cells(j, "H") = myeqn
Cells(j, "H").Interior.Color = 65535
toprow = j + 1
End If
Next
End Sub
best wsihes -- let me know if this works for you
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
ryguy7272 said:
Thanks for the tip Bernard, but it didn't seem to work for me. I must be
doing something wrong. I fiddled with it a bit, and came up with the code
below:
Sub CountUniques()
Dim sh As Worksheet, c As Range
lastrow = sh.Cells(Rows.Count, 8).End(xlUp).Row
For Each c In sh.Range("H2:H" & lastrow)
If c = "" Then
.Cells(lastrow, "H").Activate
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((r2c:R[-1]C<>"""")/(COUNTIF(r2c:R[-1]C,r2c:R[-1]C&"""")))"
Next
End Sub
To me, this looks like it should work, but it doesn't. It fails on this
line:
.Cells(lastrow, "H").Activate
Error Message = Compile Error: Invalid or unqualified reference.
I'd appreciate any help with this.
Thanks,
Ryan---
--
RyGuy
Bernard Liengme said:
Why activate a cell? Would this not work
..Cells(lastrow, "H").FormulaR1C1 = "formula"
The FREQUENCY part of your formula does not look right - only one
argument
I like to use this form
=SUMPRODUCT(--(D1
4<>""),1/COUNTIF(D1
4,D1
4&""))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
I am trying to figure out how to structure some code to be able ot count
unique numbers, all in Column H, for about 4000+ rows, and place the
count
of
the unique numbers in the first blanks space under each 'array' of
numbers
(some unique and some dupes), in Column H.
The code may be similar to this...not exactly sure...
Dim lastrow as long
For each blank in Range("H2:H4000").Select
lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
.Cells(lastrow, "H").Activate
ActiveCell.FormulaR1C1 = "=count(1/FREQUENCY(r2c:r[-1]c)"
Next blank
I am assuming uniques can be counted with this function:
=COUNT(1/FREQUENCY(H2:H4000,H2:H4000))
Any thoughts on this?
Thanks so much,
Ryan---
