Why doesn't this VBA code count the frequencies of each value?

I am trying to write a code that will:

Sort elements (Call tri1) in an array - (note that the array proceeds from a range (dimension 10,1) of values in excel.

Create three intervals of size 2.

Assign each value to its corresponding interval.

Count the number of values in each interval, and hence, I will have the frequency.

I wrote this code, but it returns values that do NOT correspond to the number of occurrences. Would anyone be able to tell what I might be missing? Or maybe something is just plain wrong in the For Each loop with If .. Then statements?


The column of values I am working with on excel is:
2
3
4
5
7
8
1
2
4
1

Please Login or Register  to view this content.

And the bubble sorting:

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

PS There's also some code missing, a sub/function called tri1.

What result are you expecting and what result are you getting?

There are a couple of things that don't quite seem right, both of which are in these lines of code.

Please Login or Register  to view this content.

First thing is that you are referring to a worksheet with the codename Sheet3 whereas in the rest of the code you've used Worksheets("Sheet3").

Second, you are resizing Destination1 to 3 rows and 1 column while plaga2 is an array with dimensions 1 to 10, 1 to 1.

Aren't there 5 items in the first interval, 3 in the second and 2 in the third?

I came up with this which seems to work to some extent.

Please Login or Register  to view this content.

I couldn't get the Match part of the code to work properly with the interval values in ascending order.

It should be easy to turn things round.

Please Login or Register  to view this content.

Yes, but for the Match to work arrintervals needs to be in descending order.

The code won't work unless arrintervals is in descending order.

If you want to display the result in ascending order use the code I posted in post #11.

Yes.

You did remove this code didn't you?

Please Login or Register  to view this content.

Can you post the exact code?

Or even better upload a sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

When I run the code in that workbook the intervals are in ascending order.

3.333333333	5
5.666666667	3
8	2

What exactly were you expecting?

How many intervals now?

What you can do is change the ReDim of plaga2 to declare the array as Long.

Please Login or Register  to view this content.

The 3 would need to be replaced by the no of intervals you now have.

PS You'll also need to delete the initial declaration of plaga2.

See what I added to the previous post when I edited it.

I've updated the code, added a NoIntervals variable to set the number of intervals and changed the ReDim of plaga2.

Please Login or Register  to view this content.

Did you remove the original Variant declaration for plaga2?

So the code works?