Code stops on certain line?? why!?

Nichole09

Board Regular
Joined
Aug 27, 2016
Messages
132
Hello,

Hoping someone can explain why this code stops on this line: "ThisWorkbook.Worksheets("Status of Closing").range("H11").value = Date" I watched this code run and it skips over this line and jumps back up to the sub and re-runs again. I am not sure why it wont finish the rest of my code? Can someone please explain what I'm doing wrong?


Code:
Private Sub worksheet_calculate()
Dim target As range
Dim xlrange As range
Dim valuetofind As String
Dim cell As range
Dim value As String
Dim nextstep As String


If ThisWorkbook.Worksheets("Status of Closing").range("A37").value = True Then
If ThisWorkbook.Worksheets("Status of Closing").range("M11") = "" Then
ThisWorkbook.Worksheets("Status of Closing").Unprotect Password:="Team"
ThisWorkbook.Worksheets("Status of Closing").range("D5").value = "Pending Review"
ThisWorkbook.Worksheets("Status of Closing").range("M11").value = "File is ready for closer to review"
ThisWorkbook.Worksheets("Status of Closing").range("H11").value = Date
ThisWorkbook.Worksheets("Status of Closing").Protect Password:="Team"


Else
ThisWorkbook.Worksheets("Status of Closing").Protect Password:="Team"
End If
End If


If ThisWorkbook.Worksheets("Status of Closing").range("Y31").value = Date Then
nextstep = "Balance CD with title"
ElseIf ThisWorkbook.Worksheets("Status of Closing").range("Y29").value = "" Then
nextstep = "Title fees to be requested"
Else
nextstep = "Balance CD with title"
End If

If ThisWorkbook.Worksheets("Status of Closing").range("V41").value = True Then
If ThisWorkbook.Worksheets("Status of Closing").range("M11") = "File is ready for closer to review" Then
ThisWorkbook.Worksheets("Status of Closing").Unprotect Password:="Team"
ThisWorkbook.Worksheets("Status of Closing").range("D5").value = "Closer Reviewing"
ThisWorkbook.Worksheets("Status of Closing").range("H11").value = Date
ThisWorkbook.Worksheets("Status of Closing").range("M11").value = nextstep
ThisWorkbook.Worksheets("Status of Closing").Protect Password:="Team"


valuetofind = ThisWorkbook.Worksheets("Status of Closing").range("D3").value
Set xlrange = ThisWorkbook.Worksheets("Tracker").range("A2:A200")
For Each cell In xlrange
If cell.value = valuetofind Then
cell.Offset(0, 6).value = ThisWorkbook.Worksheets("Status of Closing").range("D5").value
cell.Offset(0, 7).value = Date
cell.Offset(0, 9).value = ThisWorkbook.Worksheets("Status of Closing").range("H11").value
cell.Offset(0, 10).value = ThisWorkbook.Worksheets("Status of Closing").range("M11").value

End If

Next
End If
End If
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
try using, including the brackets

Code:
.value = Date()
 
Upvote 0
is it that when the code sets the cell value to the date, that triggers the worksheet calculate event - which makes the code run

overcome by adding
application.enableevents = false

near the start of the code
and
application.enableevents = true
afterwards

position these two new lines as appropriate to match what you want to achieve
 
Upvote 0
Thanks Fazza and Michael M for your replies. I tried both of these suggestions and unfortunately no luck. Michael M, I entered date as such date () and excel removes the parentheses once I hit enter. Fazza I used the application enable events in my code and it still stops on this line. I am really at a lose here. I have searched the internet endlessly for a resolution and cant seem to find the answer as to why this code will not work. What I find very odd, is why the first section of my code works perfectly and yet the second half does not. The function is almost identical. The formula's that will get these range's to change look very similar. I am really hoping someone can help explain why the second part of my code doesn't follow through. I appreciate any help.
 
Upvote 0
Try adding these 2 lines
Code:
Application.Calculation = xlCalculationManual
Application.Calculation =xlCalculationAutomatic
The first goes at the beginning of the code & the second at the end.
 
Upvote 0
Have you tried unprotecting the sheet at previous lines ??
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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