counting and loops

[narutard]

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

[Nigel]

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
>

[narutard]

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

[narutard]

anybody?

appreciate the help! thanx

[jindon]

try

Please Login or Register  to view this content.

[narutard]

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

[narutard]

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

[jindon]

You can simply refer the value like

Msgbox dic(245)

so that you can retrieve store value for 245

is this what you wanted?

[narutard]

that's great .. thanks!

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

[jindon]

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

[narutard]

awesome..thanks for the quick responses

hopefully this will be the last question

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

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

thanks

[jindon]

It shouldn't be a problem because...

Please Login or Register  to view this content.

already been converted as Double.

rgds,
jindon

[narutard]

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 me

[jindon]

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

[narutard]

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

[jindon]

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 x should hold only 1 unique item.

you can refer as dic(234)

[narutard]

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]

figured it out

thanks for all ur help Jindon!