counting variables in a loop

I'm looking for a way to count how many times my variable p changes so that I can select the value of p that changes the most. I've google and googled but havent come up with the answer so i'm back here increasing my post count.

the only reason Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4).Value = Array(x, y, z, p)
is in there is so I can see whats going on. I'd like to learn how to do this both by counting the p values in the worksheet and how to do it without using the worksheet.

Please Login or Register  to view this content.

Hi Sumplyustyc,

In your code it looks like p goes from 12 to 100 and that would be too easy an answer.

How about doing this. Put a zero in cell(2,"AA") or somewhere on a sheet. Everytime the If part is true simply do:

Please Login or Register  to view this content.

When you run a routine you can then see how many times the If part of the code was executed.

What do you mean, "how many times the variable p changes"?

The variable p is in a loop counter from 12 to 100, so I'm guessing the variable changes 89 times ... 12, 13, 14, ... 97, 98, 99, 100.

Am I missing something?

If you want to count the number of times x+y+z = p, put a counter in. A cursory look at the results indicates the larger the value of p, the more variations match p ... so it's always going to be the highest value of p that you want. So don't bother looping.

Regards

The value of p isn't changing, just the number of times it goes through the lower level loops for x and y. I think what you're saying is how many times does p match a combination of x, y and z.

As I said, it looks as though the higher the value of p, the more combinations of x, y and z will match it ... but that was wrong ;-( The value seems to oscillate.

The first highest value is 33 for p = 195 but it is also 33 for p = 203. The count never goes above zero after p=341.

Have a look at the attached sample workbook.

Regards

In what way does

it looks somethings wrong with the way it counts the farther down you go

Outputting to the sheet is a useful diagnostic tool. Unless I am mistaken, which is not unheard of, there do not appear to be any combinations of x, y and z that match p after p=340.

The following code uses some stored variables and counters rather than outputting to the sheet. The result's the same.

Please Login or Register  to view this content.

Regards

Originally Posted by TMShucks

In what way does



Regards

I guess I dont understand where those zero's come from.

for example when x + y + z = 76 there are 9 different ways x,y, and z can be expressed but your count column shows only zeros.

Whats funny is my wife was just cutting my hair and your counter looks almost exactly like I imagined I needed to do.
So thank you very much for doing that its exactly what I wanted.

for example when x + y + z = 76 there are 9 different ways x,y, and z can be expressed but your count column shows only zeros.

No, it doesn't ....look at columns F and G, row 66: p unique = 76, count = 9

p unique count
12 1
13 0
14 1
15 1
16 1
17 1
18 1
19 2
20 1
21 3
22 2
23 2
24 2
25 2
26 3
27 3
28 2
29 4
30 2
31 5
32 2
33 5
34 3
35 4
36 5
37 3
38 5
39 5
40 5
41 5
42 4
43 6
44 5
45 5
46 7
47 4
48 7
49 4
50 9
51 6
52 6
53 8
54 5
55 9
56 6
57 8
58 7
59 8
60 7
61 9
62 7
63 10
64 7
65 10
66 6
67 11
68 8
69 9
70 11
71 8
72 10
73 10
74 10
75 10
76 9
77 13
78 8
79 12
80 12
:

Regards

You're welcome. Thanks for the rep.