counting and loops
 

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

counting and loops
 

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

counting and loops
 

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

counting and loops
 

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

counting and loops
 

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

counting and loops
 

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

counting and loops
 

please.. this is a very simple question ..i thin

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

counting and loops
 

You can simply refer the value like

Msgbox dic(245)

so that you can retrieve store value for 245

is this what you wanted

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

counting and loops
 

that's great .. thanks!

another silly question.. how can u check how many items are stored in
dic?


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

counting and loops
 

dic.count will give you the number of items stored in dictionary objec

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

counting and loops
 

awesome..thanks for the quick responses

hopefully this will be the last question

everything that i stored in the array are numbers .. i was wonderin
how to change the type to numbers (i think its saved as objects righ
now- i dunno if that makes a difference)

but i want to change them to numbers because i want to do some dat
analysis (use some equations and other functions)- how do i go abou
doing it? specifically.. i want to change y(i) into numbers (fro
previous examples)

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

counting and loops
 

It shouldn't be a problem because...


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

dic(a(i, 1)) = Val(dic(a(i, 1))) + 1

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


already been converted as Double.

rgds,
jindon


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

counting and loops
 

it keeps giving me an error - type 13

maybe if i show u my code ..that might help

For i = 0 To dic.Count

If x(i) = 245 Then
counter = counter + 1
unknown = -1 * (y(i) - 1)
place = place + y(i)
Range("d" & place).Select
temp2 = "=countif(R[" & unknown & "]C[-3]:RC[-3],""azw"")"
Cells(counter, "g") = temp2
Cells(counter, "h") = y(i) - temp2

End If
Next

temp2 line = some reason the var unnown doesn't save the y(i) variable
cells(counter,"h") line i get type 13 error

so i'm thinking its the type of variable..i dunno..

hopefully u can enlighten m

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

counting and loops
 

dic.count always from 1, not 0

but, x , dic.keys and y, dic.items from 0

therefore you need to adjust the counter

For i = 1 To dic.Count

If x(i-1) = 245 Then
counter = counter + 1
unknown = -1 * (y(i-1) - 1)
place = place + y(i-1)
Range("d" & place).Select
temp2 = "=countif(R[" & unknown & "]C[-3]:RC[-3],""azw"")"
Cells(counter, "g") = temp2
Cells(counter, "h") = y(i-1) - temp

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

counting and loops
 

hate to be ungrateful cuz i'm really appreciating your help

but i'm stil getting an error 13- type mismatch

specifically at the last line

For i = 1 To dic.Count

If x(i-1) = 245 Then
counter = counter + 1
unknown = -1 * (y(i-1) - 1)
place = place + y(i-1)
Range("d" & place).Select
temp2 = "=countif(R[" & unknown & "]C[-3]:RC[-3],""azw"")"
Cells(counter, "g") = temp2
CELLS(COUNTER, \"H\") = Y(I-1) - TEMP2

thanks a bunch


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

counting and loops
 

If x(i-1) = 245 Then
counter = counter + 1
unknown = -1 * (y(i-1) - 1)
place = place + y(i-1)
Range("d" & place).Select
temp2 = "=countif(R[" & unknown & "]C[-3]:RC[-3],""azw"")"
Cells(counter, "g") = temp2
Cells(counter, "h") = y(i-1) - temp2

try

while you are in the VB editor:
go ot View - Local Window then click on somewher on the sub routine,

hit F8 will execute the sub routine line by line and you can see the
movements of
all the variables in the Local Window.

I guess the problem is "temp2", is this String?
Cells(counter, "g").FormaulR1C1="=countif(R[" & unknown &
"]C[-3]:RC[-3],""azw"")"

anyway you cannot subtract String value if temp2 is "azw"

and you don't need to loop, because y should hold only 1 unique item.


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

counting and loops
 

hey

you were right about the temp2 variable

for some reason its saving it as a string

i can't seem to figure out why.. can u help me out .. temp2 is supposed
to be a number! and i can't get the countif formula to work


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

counting and loops
 

figured it out

thanks for all ur help Jindon!


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