Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops... | Excel Discussion (Misc queries) | |||
Do loops | Excel Discussion (Misc queries) | |||
Using For - Next Loops in VB | New Users to Excel | |||
Do Loops | Excel Programming | |||
Do Loops | Excel Programming |