Macro Causes Excel to Not respond

[Jared_Jones_23]

Hello,
I am relatively new to VBA and I have written a macro code that searches certain cells and then puts a formula into different cells that match the criteria. My macro is searching through thousands of cells and gets hung up and freezes whenever its run. Here the part of the code that is getting the error. If anyone has any suggestions I would appreciate it.
Thank You
Jared

Private Sub Workbook_Open()
Dim lastRow, lastCol As Long
Dim i, j As Integer

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.count
lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.count
count = 0
msg = "Work"

For i = 20 To lastRow
For j = 53 To lastCol
If Cells(i, j) <= Cells(17, j) And Cells(i, 14) >= Cells(18, j) Then
Cells(i, j) = (Cells(18, j) - Cells(17, j) + 1) / (Cells(i, 14) - Cells(i, 13) + 1) * Cells(i, 18) * Cells(i, 20)
End If
Next j
Next i

With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[Jerry Sullivan]

Hello and Welcome,

One thing that might help is to work with the data range instead of referencing the UsedRange Property.

UsedRange can be much larger and include cells have formatting but no data, or cells that had values that were deleted long ago.

Try replacing:

lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

With this:

lastCol = Cells.Find(What:="*", After:=Range("A1"), _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lastRow = Cells.Find(What:="*", After:=Range("A1"), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

There are a few other things that are of concern, but I don't want overwhelm you with feedback on your first post here!