MasterOfTheSky
asked on
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
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
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
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
Let me know if you have questions.
sdwalker
Tester2.xls
I realized I didn't fully answer your question because I didn't provide a message box. Attached is revised file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simonwait you code say variable not define.
ASKER
sdwalker you forgot the code to make it print the invoice.
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
I trust you're kidding about the invoice.
ASKER
My mistake sdwalker. I am sorry. Ignore what I said before.
ASKER
Simonwait you code give me a runtime error 6, overflow. Please fix as I would like to assign a grade to this question ASAP.
ASKER
Great Job!! Thank you very much!!
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 and =.1 and <0-2
etc