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

 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

MartinKo

New Member
Joined
Jan 23, 2014
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,395
Members
414,063
Latest member
N_Bates

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
Top