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

Have you considered using Data-Subtotals command ?

--
Cheers
Nigel



"narutard" wrote in
message ...

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 wil
use it to filter and to a countif command ...

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

thank

--
narutar
-----------------------------------------------------------------------
narutard's Profile: http://www.excelforum.com/member.php...fo&userid=2511
View this thread: http://www.excelforum.com/showthread.php?threadid=38758

anybody?

appreciate the help! thanx


--
narutard
------------------------------------------------------------------------
narutard's Profile: http://www.excelforum.com/member.php...o&userid=25111
View this thread: http://www.excelforum.com/showthread...hreadid=387584

try

Code:
--------------------

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

--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=387584

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? specficiall
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 variabl
(i'm not familar w/ the syntax of VB! sorry!)

thank

--
narutar
-----------------------------------------------------------------------
narutard's Profile: http://www.excelforum.com/member.php...fo&userid=2511
View this thread: http://www.excelforum.com/showthread.php?threadid=38758