Macro Doesn't Run Automatically

[matthewlouis]

Several months ago, I had some help creating the following macro.

Sub clicky()
For t = 1 To 100
Application.Calculate
Range("J10").Value = Range("J10").Value + 1
If Range("F11").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1
Debug.Print t & ":" & Range("F11")
MsgBox t
Next t
End Sub

The macro works well except for one thing. It doesn't run automatically. When I hit CTRL-Shift-C, I want it to run the macro automatically 100 times. However, I am having to hit the Esc button each time to run ONE simulation, or 100 times to complete it. Am I missing a line in the code? I circled back to the person who helped write this code but I never heard back from him . . . it's been 3 months . . . so I hope the moderators understand this isn't a re-post.

Thanks in advance!

Several months ago, I had some help

 

[Zyndstoff]

you can re-enter the Debug.Print t & ":" & Range("F11") line, it won't stop the macro but it has no effect whatsoever on the calculation.

What your code does is this:

1. Step: Calculate the Worksheet
2. Step: Add 1 to cell J10
3. Step: Check for cell F11 <= 0.8, if so add 1 to cell J12
4. Step: Go to 1. step

What would be of interest: how and when (on what event) is the value of cell F11 calculated?

 

[matthewlouis]

Your question: What would be of interest: how and when (on what event) is the value of cell F11 calculated?

I have historical monthly performance of the stock and bond markets. Given a certain withdrawal rate on the portfolio with the history, I want to run 100 simulations to see how many times F11 in percentage terms) the portfolio was successful more than 80% (0.8) of the time to Age 95.

K2 is always 100%

J10 = Number of simulations that is shown in the code as For t = 1 To 100

J12 = how many simulations came in at less than 80% (this is shown in the code as If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1< 0.80

I have cells that show 77% and 23%. So, F11 is result I want . . . it means 77% of the time there was an 80% chance the portfolio (0.80 in code) lasted to at least Age 95.

Hope this helps! The algo works great . . . it just doesn't automatically run the simulations 100 times, I have to run the algo, then hit Esc 100 times.

What do you think? Thank you for your help on this!

 

[Zyndstoff]

I still don't understand. You didn't answer HOW F11 is calculated and when.
There must be either an underlying VBA-Code somewhere or an Excel-formula in F11.

Also: how is the Sub "Clicky()" started?

The two lines that were recommended to delete have no influence on the calculation. None.

 

[matthewlouis]

Sorry, I guess I didn't understand . . .

F11 is calculated with the code line . . . If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1 getting subtracted from K2 (100%)

That is F11 . . . it's 100% minus the code above.

Sub "Clicky()" is started by Alt-F8 and then I click on Run

 

[Zyndstoff]

As far as I see the code If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1 has no influence on F11. Also, in your initially posted code there is no such line.
That line only changes the value of J12...?!? Without knowing all the code and all Excel-functions in the cells nobody will be able to help..

 

[matthewlouis]

OK, let me dig deeper and I'll get back.

I really appreciate your help.

 

[matthewlouis]

OK, I'm sorry . . . the reference to F11 isn't even needed!! I took it out, the code still works, still isn't automated. Here's the code I have now.

Sub clicky()
For t = 1 To 100
Application.Calculate
Range("J10").Value = Range("J10").Value + 1
If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1
MsgBox t
Next t
End Sub

When I took the MsgBox t line out per what Joe said earlier, it messed up the calculation (?) so I put it back

 

[Zyndstoff]

Again, the MsgBox has no influence on any calculation. It just displays a box showing the current value of t. Nothing more.
You can take it out.

But something just occured to me: maybe you have accidently put a Stop somewhere in your code? That should look something like this:

In order to remove the stop, if there is any, just click on the left bold dot and save the workbook.

Still, I don't see K2 calculated and changed by your code.

There have to be Excel-formulas in the Worksheet.

 

[matthewlouis]

Interesting.

K2 is a cell where I just manually put 100%. It is not an excel calculation. I use it subtract the code calculation from to give me the probability.

I do not have a bold dot anywhere so I don't have a stop in there.

Again, this is the code I have now. It works, just not automated and I don't have a blue dot (a stop).:

Sub clicky()
For t = 1 To 100
Application.Calculate
Range("J10").Value = Range("J10").Value + 1
If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1
Next t
End Sub

 

[Zyndstoff]

If you put a 100% in K2 and the value is not calculated, then the line

If Range("k2").Value <= 0.8 Then Range("J12").Value = Range("J12").Value + 1

has no effect, since K2 will never change from 100%.

I give up, you do not provide sufficient information to even understand the problem.