Macro to Recalculate a Sheet and check value of a cell

[Faseeh]

Dear All,

I hope this has not been answered on this forums, I tried to search for a solution but have reached this far (see below code).

The problem: I have sheet where i want to simulate a certain process. I generate random numbers and want to compare it for a value in a cell. Lets say i want a number to be generated between 1 and 5 and repeat this process 10 times - criteria being number 3. Now i am looking to write a macro that should recalculate sheet 10 times (recalculate because i have used randbetween function to generate random numbers), but meanwhile calculating the sheet, if the random number comes to be 3, it should stop and report the instance.

Here is the code that i have written, there is problem with the macro until i compare values.

Sub Test()
Dim n As Integer
n = ActiveSheet.Range("C6").Value
For i = 1 To n
    ActiveSheet.Calculate
        If Range("B4").Value = 0 Then MsgBox "Incorrect Value"
    Exit Sub
Next i
End Sub

Note that Just to test the macro, i wanted to display the msg box. The random number is generated in cell B4 means B4 has formula =RANDBETWEEN(1,5).
Similarly C6 contains the times sheet to be recalculate and in cell C7 i want the instance when criteria is meet.

Thanks alot in advace!!
Faseeh

 

[r1998]

Hello Faseeh
random numbers will be generated in column A, starting from a1
if cell value is 3, then in adjacent b column instance number will be placed, n then it will break out of loop

Sub Macro1()
Dim i As Integer
Range("a1:b10").Value = ""
For i = 1 To 10
Cells(i, 1).Value = Application.WorksheetFunction.RandBetween(1, 5)
If Cells(i, 1).Value = 3 Then
Cells(i, 2).Value = i
Exit For
End If
Next i
End Sub

 

[Faseeh]

Thank you so much for the response, i think i have not explained thing quite well.....

Cell B4 = RandBetween(1, 5)
Cell B5 = RandBetween(1, 5)
Cell B6 = RandBetween(1, 5)
Cell B7 = SUM(B4:B6)

I press F9 to recalculate the cells B4:B6 and see if sum is 3 (this criteria is in cell C12). Now the problem is that i have to press F9 about 400 times to see when the criteria is matched and that is really tedious.

What i want is to have that F9 recaulcate automated and if criteria match then macro should stop and report the instance in cell C11.

Thanks in advance i know its difficult to do without sample workbook but i an in my office and can't share workbooks. I will definiately share when will be at home.

Thanks alot.

 

[r1998]

Hello Faseeh
I have in cells
B4 = RandBetween(1, 5)
B5 = RandBetween(1, 5)
B6 = RandBetween(1, 5)
B7 = SUM(B4:B6)
c6 = 1000 ( number of iterations )
c7 = iteration number when b7 = 3
for that use this code

Sub Macro1()
Dim i As Integer


For i = 1 To Range("c6").Value
ActiveSheet.Calculate


If Range("b7").Value = 3 Then
Range("c7").Value = i
Exit For
End If


Next i


End Sub

this will give the correct result in cell c7, but the problem here is that when b7 is 3, it will give the instance in c7 n break out of loop but bcoz value in c7 is changed it will recalculate values again, hence in b7 u wont find the value 3.... hence using msgbox would be a better option for checking

Sub Macro1()

Dim i As Integer


For i = 1 To Range("c6").Value
ActiveSheet.Calculate
If Range("b7").Value = 3 Then
MsgBox (i)
Exit For
End If


Next i
End Sub

 

[Faseeh]

Sorry for such a late feedback.

This is spot on!!! Thank you so much Sir r1998!!

I am also thankful for the explanation you provided that was a learning point.

Yours truly,
Faseeh

 

[r1998]

Thank you Faseeh
Glad to be of help