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
MasterOfTheSkyAsked:
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.

Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndCommented:
the ranges are
>=0 and =.1 and <0-2
etc
0
simonwaitCommented:
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

Open in new window

0
sdwalkerCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdwalkerCommented:
I realized I didn't fully answer your question because I didn't provide a message box.  Attached is revised file.
0
sdwalkerCommented:
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.

Start your 7-day free trial
MasterOfTheSkyAuthor Commented:
Simonwait you code say variable not define.  
0
MasterOfTheSkyAuthor Commented:
sdwalker you forgot the code to make it print the invoice.  
0
simonwaitCommented:
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

Open in new window

Copy-of-Tester1.xls
0
sdwalkerCommented:
I trust you're kidding about the invoice.
0
MasterOfTheSkyAuthor Commented:
My mistake sdwalker.  I am sorry.  Ignore what I said before.
0
MasterOfTheSkyAuthor 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
MasterOfTheSkyAuthor Commented:
Great Job!! Thank you very much!!
0
simonwaitCommented:
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

Open in new window

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.