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---


--
RyGuy

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:D4<""),1/COUNTIF(D1:D4,D1:D4&""))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
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---


--
RyGuy

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" wrote:

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:D4<""),1/COUNTIF(D1:D4,D1:D4&""))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
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---


--
RyGuy

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" wrote in message
...
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" wrote:

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:D4<""),1/COUNTIF(D1:D4,D1:D4&""))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
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---


--
RyGuy

I am in awe!!! I have to study the logic and learn from this...
Thanks so much!!
Ryan---


--
RyGuy


"Bernard Liengme" wrote:

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" wrote in message
...
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" wrote:

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:D4<""),1/COUNTIF(D1:D4,D1:D4&""))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
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---


--
RyGuy