How do I put Msgbox in between formulas in VBA?

[genracela]

I have a simple formula in VBA

1. How do I put Msgbox, asking "Start in Last Row?" If Yes Start in the Last row, If No start in Row 4.

And How do I insert the formula that will start the calculation in the last row if yes, and formula that will start the calculation in row 4?

2. How do I put Msgbox, asking "Continue Calculation?" If Yes continue with next function, If no end run.


[vba]Option Explicit
Sub Calculate()
Range("D37").Formula = "=A3+B3+C3"
'How do I put Msgbox in this line, asking "Continue Calculation?" If Yes continue with next function, If no end run.
Range("D712").Formula = "=A7+B7+C7"
Range("D1318").Formula = "=A13+B13+C13"
Range("D1923").Formula = "=A19+B19+C19"
End Sub
[/vba]

Thanks for your time! I really appreciate all the help I get form this site!

[Simon Lloyd]

The things you ask are very simple to do, but you need to explain yourself better, in your code you want the formula to be put in to D37, you the overwrite D7, so how do you want to place the formula? should it be in column D last unused cell then down for 20 rows?

[genracela]

Actually, my purpose is just to have a pause in every 500 rows.

I have like 20,000 cells to calculate, and it takes 45 minutes to calculate all the cells. So, What I want is to pause in every 500 rows, so I have an option to stop or continue.

Then, when I stopped or click "No" in my MsgBox, lets say in row 500, and decided to the VBA again I want to have an option to choose if I want to start over again for my calculation or start in the row where I stopped my first calculation.

Thank you very much for your time!

[mdmackillop]

Do you mean your code takes 45 minutes to run or the sheet takes 45 minutes to calculate?

[genracela]

The sheet and yes the code also.

[mdmackillop]

A code break is simple enough, just add a counter

[VBA]
Sub TestBreak()
Dim i As Long
For i = 1 To Rows.Count
Cells(i, 1) = i
If i Mod 500 = 0 Then
If MsgBox("Continue?", vbYesNo) = vbNo Then Exit Sub
End If
Next
End Sub

[/VBA]

[genracela]

Thank you mdmckillop!

How about if I click "No" and stopped. But, then I decided to continue calculation? Will it start in the beginning again or will it start in the last row where I stopeed my calculation the first time?

If no, how will I go about it?

Thank you again!

[Simon Lloyd]

Not tested but how about something like this:
(using mdmckillops' suggestion)
[vba]Public ans As Long
Sub TestBreak()
Dim i As Long
If MsgBox("Calculate from start?", vbYesNo, "Continue iteration") = vbYes Then
ans = 1
End If
For i = ans To Rows.Count
Cells(i, 1) = i
If i Mod 500 = 0 Then
If MsgBox("Continue?", vbYesNo) = vbNo Then
ans = i
Exit Sub
end if
End If
Next
End Sub[/vba]

[genracela]

Ok I'll try this code.

Thanks so much to both of you!!!

[genracela]

I'm sorry to bother you agian, but I added the formulas below and I just can't get it right.

I already tried inserting the formula in different line but I just couldn't get it.

Attached is my sample file. Thanks again!

[VBA]Option Explicit
Public ans As Long
Sub TestBreak()
Dim i As Long
If MsgBox("Calculate from start?", vbYesNo, "Continue iteration") = vbYes Then
ans = 1
End If

Range("C3:C10").Formula = "=A3+B3"

For i = ans To Rows.Count
Cells(i, 1) = i
If i Mod 500 = 0 Then
If MsgBox("Continue?", vbYesNo) = vbNo Then
ans = i
Exit Sub
End If
End If

Next

Range("C11:C18").Formula = "=A11+B11"
For i = ans To Rows.Count
Cells(i, 1) = i
If i Mod 500 = 0 Then
If MsgBox("Continue?", vbYesNo) = vbNo Then
ans = i
Exit Sub
End If
End If

Next
Range("C19:C24").Formula = "=A11+B11"
End Sub
[/VBA]

[Bob Phillips]

If it takes 45 minutes to calculate, shouldn't we be addressing that, not adding breaks?

[Simon Lloyd]

If it takes 45 minutes to calculate, shouldn't we be addressing that, not adding breaks?

naturally you're right Bob, the worksheet/workbook must be full of volatile formulae, i guess we need a sample workbook with actual formulae and to understand the process from the OP i.e does he calculate one sheet or all? is there a need for all.....etc

[genracela]

Thanks you for finding time to solve my concern!

Attached is my sample file with actual formula.

i deleted 90% of data so file will be small enough to attach. But this is the actual file that I'm trying to work on.

The formula should calculate all the way down to the last row.

It also has a VBA for the dates.

Thank you so much!