# Excel VBA Question

The file Tester1.xls in column A contains 50,000 random numbers. (To generate the random number, Excel's RAND function was used and then "froze" them by copying and paster values only over the formulas).

I am trying to write a sub that creates an array called frequency of size 10.  It will then populate the array by looping over the random numbers and putting them into "bins."  Specifically, frequency(1) should contain the count of all random numbers from 0 to 0.1, frequency(2) should contain the count of all random number from 0.1 to 0.2, and so on.  Because every random number has to be in one of these bins, the counts should add to 50,000.  The final contents of the array should be reported in a message box. (How should you deal with the endpoints such as 0.1? It doesn't really matter, because there is virtually no chance that a random number will fall exactly on one of the breakpoints.)
Tester1.xlsx
Asked:
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
the ranges are
>=0 and =.1 and <0-2
etc
0
Commented:
Do you mean something like this?  The Bin Total is just to check that all the bins add up to 50000
``````Sub sorttobins()
For Each acell In Range("A1:A50000")
Bin = Mid(acell, 3, 1)
Select Case Bin
Case 0
bin0 = bin0 + 1
Case 1
bin1 = bin1 + 1
Case 2
bin2 = bin2 + 1
Case 3
bin3 = bin3 + 1
Case 4
bin4 = bin4 + 1
Case 5
bin5 = bin5 + 1
Case 6
bin6 = bin6 + 1
Case 7
bin7 = bin7 + 1
Case 8
bin8 = bin8 + 1
Case 9
bin9 = bin9 + 1
End Select
Next acell
BinTotal = bin0 + bin1 + bin2 + bin3 + bin4 + bin5 + bin6 + bin7 + bin8 + bin9
MsgBox ("Bin 0 - 0.1      = " & bin0 & vbNewLine _
& "Bin 0.1 - 0.2   = " & bin1 & vbNewLine _
& "Bin 0.2 - 0.3   = " & bin2 & vbNewLine _
& "Bin 0.3 - 0.4   = " & bin3 & vbNewLine _
& "Bin 0.4 - 0.5   = " & bin4 & vbNewLine _
& "Bin 0.5 - 0.6   = " & bin5 & vbNewLine _
& "Bin 0.6 - 0.7   = " & bin6 & vbNewLine _
& "Bin 0.7 - 0.8   = " & bin7 & vbNewLine _
& "Bin 0.8 - 0.9   = " & bin8 & vbNewLine _
& "Bin 0.9 - 1      = " & bin9 & vbNewLine & vbNewLine _
& "Bin Total Check    = " & BinTotal)

End Sub
``````
0
Commented:
I believe this does what you're looking for, but I'm not sure how you wanted everything formatted.  Remember, you can't have code in xlsx files.
Let me know if you have questions.
sdwalker

Tester2.xls
0
Commented:
I realized I didn't fully answer your question because I didn't provide a message box.  Attached is revised file.
0
Commented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Simonwait you code say variable not define.
0
Author Commented:
sdwalker you forgot the code to make it print the invoice.
0
Commented:
Try this instead.  Think your last comment to sdwalker may not relate to this quesiton
``````Sub sorttobins()
Dim acell As Range
Dim bin As Integer
Dim bin0 As Integer
Dim bin1 As Integer
Dim bin2 As Integer
Dim bin3 As Integer
Dim bin4 As Integer
Dim bin5 As Integer
Dim bin6 As Integer
Dim bin7 As Integer
Dim bin8 As Integer
Dim bin9 As Integer
Dim bintotal As Integer

For Each acell In Range("A1:A50000")
bin = Mid(acell, 3, 1)
Select Case bin
Case 0
bin0 = bin0 + 1
Case 1
bin1 = bin1 + 1
Case 2
bin2 = bin2 + 1
Case 3
bin3 = bin3 + 1
Case 4
bin4 = bin4 + 1
Case 5
bin5 = bin5 + 1
Case 6
bin6 = bin6 + 1
Case 7
bin7 = bin7 + 1
Case 8
bin8 = bin8 + 1
Case 9
bin9 = bin9 + 1
End Select
Next acell
bintotal = bin0 + bin1 + bin2 + bin3 + bin4 + bin5 + bin6 + bin7 + bin8 + bin9
MsgBox ("Bin 0 - 0.1      = " & bin0 & vbNewLine _
& "Bin 0.1 - 0.2   = " & bin1 & vbNewLine _
& "Bin 0.2 - 0.3   = " & bin2 & vbNewLine _
& "Bin 0.3 - 0.4   = " & bin3 & vbNewLine _
& "Bin 0.4 - 0.5   = " & bin4 & vbNewLine _
& "Bin 0.5 - 0.6   = " & bin5 & vbNewLine _
& "Bin 0.6 - 0.7   = " & bin6 & vbNewLine _
& "Bin 0.7 - 0.8   = " & bin7 & vbNewLine _
& "Bin 0.8 - 0.9   = " & bin8 & vbNewLine _
& "Bin 0.9 - 1      = " & bin9 & vbNewLine & vbNewLine _
& "Bin Total Check    = " & bintotal)

End Sub
``````
Copy-of-Tester1.xls
0
Commented:
I trust you're kidding about the invoice.
0
Author Commented:
My mistake sdwalker.  I am sorry.  Ignore what I said before.
0
Author Commented:
Simonwait  you code give me a runtime error 6, overflow.   Please fix as I would like to assign a grade to this question ASAP.
0
Author Commented:
Great Job!! Thank you very much!!
0
Commented:
Too late now I suppose but for reference here you are
``````Sub sorttobins()
Dim acell As Range
Dim bin As Long
Dim bin0 As Long
Dim bin1 As Long
Dim bin2 As Long
Dim bin3 As Long
Dim bin4 As Long
Dim bin5 As Long
Dim bin6 As Long
Dim bin7 As Long
Dim bin8 As Long
Dim bin9 As Long
Dim bintotal As Long

For Each acell In Range("A1:A50000")
bin = Mid(acell, 3, 1)
Select Case bin
Case 0
bin0 = bin0 + 1
Case 1
bin1 = bin1 + 1
Case 2
bin2 = bin2 + 1
Case 3
bin3 = bin3 + 1
Case 4
bin4 = bin4 + 1
Case 5
bin5 = bin5 + 1
Case 6
bin6 = bin6 + 1
Case 7
bin7 = bin7 + 1
Case 8
bin8 = bin8 + 1
Case 9
bin9 = bin9 + 1
End Select
Next acell
bintotal = bin0 + bin1 + bin2 + bin3 + bin4 + bin5 + bin6 + bin7 + bin8 + bin9
MsgBox ("Bin 0 - 0.1      = " & bin0 & vbNewLine _
& "Bin 0.1 - 0.2   = " & bin1 & vbNewLine _
& "Bin 0.2 - 0.3   = " & bin2 & vbNewLine _
& "Bin 0.3 - 0.4   = " & bin3 & vbNewLine _
& "Bin 0.4 - 0.5   = " & bin4 & vbNewLine _
& "Bin 0.5 - 0.6   = " & bin5 & vbNewLine _
& "Bin 0.6 - 0.7   = " & bin6 & vbNewLine _
& "Bin 0.7 - 0.8   = " & bin7 & vbNewLine _
& "Bin 0.8 - 0.9   = " & bin8 & vbNewLine _
& "Bin 0.9 - 1      = " & bin9 & vbNewLine & vbNewLine _
& "Bin Total Check    = " & bintotal)

End Sub
``````
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.