counting and loops

Have you considered using Data->Subtotals command ?

--
Cheers
Nigel



"narutard" <[email protected]> wrote in
message news:[email protected]...
>
> hello
>
> i need help with programming a module that will count a column by
> comparing the value after it and saving the number of rows it counted
> before a different value occured in addition with saving the row number
> to a variable at the row right before the different value occured.
> this will occur throughout the whole column
>
> example
>
> 245
> 245
> 245
> 245
> 245
> 250
> 250
> 250
> 260
> 260
> so the loop (as i would imagine) would count the number of rows for 245
> which is 5 and say it occured on row 26 which will save row 26 to some
> variable then it will count 250 which is 3 and so forth
>
> any help would be greatly appreciated
>
>
> --
> narutard
> ------------------------------------------------------------------------
> narutard's Profile:
http://www.excelforum.com/member.php...o&userid=25111
> View this thread: http://www.excelforum.com/showthread...hreadid=387584
>

because i need to retain all these values to a variable in which i will use it to filter and to a countif command ...

therefore i need to figure out how to do that count and loop

thanks

anybody?

appreciate the help! thanx

try

Sub test()
Dim dic As Object, x, y, a, i
Set dic = CreateObject("Scripting.Dictionary")
a = Range("a1", Range("a65536").End(xlUp)).Value
For i = LBound(a, 1) To UBound(a, 1)
    If Not IsEmpty(a(i, 1)) And Not dic.exists(a(i, 1)) Then
        dic.Add a(i, 1), 1
    Else
        dic(a(i, 1)) = Val(dic(a(i, 1))) + 1
    End If
Next
If dic.Count < 1 Then Exit Sub
x = dic.keys: y = dic.items
With Range("c1")
    .CurrentRegion.ClearContents
    .Resize(UBound(x) + 1).Value = Application.Transpose(x)
    .Offset(, 1).Resize(UBound(y) + 1).Value = Application.Transpose(y)
End With
Set dic = Nothing: Erase a, x, y
End Sub

great! the last suggestion worked

now i have a pretty n00b question

i want to call a value from the array ..how do i do that? specficially i want to call that value into another variable

i.e. say i want the 2nd value of Y.. how do u save it to a variable (i'm not familar w/ the syntax of VB! sorry!)

thanks