VBA - random number checker

MartinKo

New Member
Joined
Jan 23, 2014
Messages
8
Dear All,

First of all, let me introduce myself, as i am new in this forum. I am 23 and currently working as demand forecasting intern.

i am trying to learn VBA, so I read through some tutorial and now i am working on simple projects. In order to undestrand loop, I want to develop macro that would create list of "n" random numbers ("n" located in A1) in the column A. Than it will check how many times the number "N" (N is located in B1) occurred in the list from the column A.

here is my code:
Sub NameX()

Dim Row, RNB As Integer

For Row = 1 To Cells(1, 1)
Cells(Row, 1).Offset(1, 0) = RNB
RNB = Int((36 - 0 + 1) * Rnd + 0)
If Cells(Row, 1) = Cells(1, 2) Then
Cells(Row, 2).Offset(1, 0) = "TRUE"
Else
Cells(Row, 2).Offset(1, 0) = "False"
End If
Next Row
Range("D2").Value = Application.WorkSheetFunction.CountIf (Range("A2:A" & Cells(1,1) + 1), "TRUE")/Application.WorkSheetFunction.Count (Range("A2:A" & Cells(1,1)+1)
End Sub

Problems:
Build in functions does not working
TRUE is written in the cells where numbers are not matching
FALSE is shown when there is different number from number in B1.

I am just beginner, so there are probably many mistakes, so i want to get rid of any wrong practices.

Thank you for any advice and support.

MartinKo

 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
MartinKo,

Welcome to MrExcel.

Some comments.....
You have a line that sets cell A = RNB before the line that generates RNB. So A2 will always be 0 and A3 will be the first random number noty the second etc etc. So those two lines need to be swapped places.
Because your loop count variable, Row, is starting from 1 you have three lines where you have correctly used an Offset to get the actual row of interest but in the If statement that checks if the number = B1 you have used Row where it should be Row +1 to gve a similar offset and therefore a propper comparison.

Finally, in the worksheet functions you are testing ranges in A where you should be testing B. And I think you should be using COUNTA not Count. Also on the assumption that you are looking to get a fractional or percentage, why not use value in A1 directly as the divisor?

Rich (BB code):
Sub NameX()
Dim Row, RNB As Integer
For Row = 1 To Cells(1, 1)
RNB = Int((36 - 0 + 1) * Rnd + 0)
Cells(Row, 1).Offset(1, 0) = RNB
If Cells(Row + 1, 1) = Cells(1, 2) Then
Cells(Row, 2).Offset(1, 0) = "TRUE"
Else
Cells(Row, 2).Offset(1, 0) = "False"
End If
Next Row
Range("D2").Value = Application.WorksheetFunction.CountIf(Range("B2:B" & Cells(1, 1) + 1), "TRUE") _
/ Application.WorksheetFunction.CountA(Range("B2:B" & Cells(1, 1) + 1))

'??????
Range("E2").Value = Application.WorksheetFunction.CountIf(Range("B2:B" & Cells(1, 1) + 1), "TRUE") _
/ Range("A1")
'??????
End Sub

Hope that helps.
 
Upvote 0
MartinKo,

Welcome to MrExcel.

Some comments.....
You have a line that sets cell A = RNB before the line that generates RNB. So A2 will always be 0 and A3 will be the first random number noty the second etc etc. So those two lines need to be swapped places.
Because your loop count variable, Row, is starting from 1 you have three lines where you have correctly used an Offset to get the actual row of interest but in the If statement that checks if the number = B1 you have used Row where it should be Row +1 to gve a similar offset and therefore a propper comparison.

Finally, in the worksheet functions you are testing ranges in A where you should be testing B. And I think you should be using COUNTA not Count. Also on the assumption that you are looking to get a fractional or percentage, why not use value in A1 directly as the divisor?

Rich (BB code):
Sub NameX()
Dim Row, RNB As Integer
For Row = 1 To Cells(1, 1)
RNB = Int((36 - 0 + 1) * Rnd + 0)
Cells(Row, 1).Offset(1, 0) = RNB
If Cells(Row + 1, 1) = Cells(1, 2) Then
Cells(Row, 2).Offset(1, 0) = "TRUE"
Else
Cells(Row, 2).Offset(1, 0) = "False"
End If
Next Row
Range("D2").Value = Application.WorksheetFunction.CountIf(Range("B2:B" & Cells(1, 1) + 1), "TRUE") _
/ Application.WorksheetFunction.CountA(Range("B2:B" & Cells(1, 1) + 1))

'??????
Range("E2").Value = Application.WorksheetFunction.CountIf(Range("B2:B" & Cells(1, 1) + 1), "TRUE") _
/ Range("A1")
'??????
End Sub

Hope that helps.

Thank you very much Tony. Really good explanation.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top