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

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

"ryguy7272" <(E-Mail Removed)> wrote in message
news:C246F784-3D65-43FF-9CEA-(E-Mail Removed)...
>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(--(D14<>""),1/COUNTIF(D14,D14&""))
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:C246F784-3D65-43FF-9CEA-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:47ABF3CB-FAA2-43D3-B548-(E-Mail Removed)...
> 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(--(D14<>""),1/COUNTIF(D14,D14&""))
>>
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "ryguy7272" <(E-Mail Removed)> wrote in message
>> news:C246F784-3D65-43FF-9CEA-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
> news:47ABF3CB-FAA2-43D3-B548-(E-Mail Removed)...
> > 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(--(D14<>""),1/COUNTIF(D14,D14&""))
> >>
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> Microsoft Excel MVP
> >> http://people.stfx.ca/bliengme
> >> remove caps from email
> >>
> >> "ryguy7272" <(E-Mail Removed)> wrote in message
> >> news:C246F784-3D65-43FF-9CEA-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>
>