Macro works stepping through but not when run

[Ordnassela]

Hey guys, fairly new to VBA and this forum. I'm running into some issues running my code-it seems like it only works when I press F8.

It does exactly what I need it to do, but when I press play, Excel freezes. Any thoughts on how I can fix it?

Sub Depreciation1to5()
'
' This Macro is for Depreciating in FAR

Dim tDate As Date
Dim cel As Range
Dim rng As Range

'Enter latest month here
'#5/31/2021#
'#6/30/2021#
tDate = #4/30/2021#

'Filter for the last month
    Sheets("CUR FAR").Range("$A$1:$V$2474").AutoFilter Field:=20, Operator:= _
        xlFilterValues, Criteria2:=Array(1, tDate)

'Copy and Paste Special Value
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 17).Select
    Range(Selection, Selection.End(xlDown)).Select
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    For Each cel In rng
    cel.Value = cel.Value
    Next cel
  
'Clear MO DEPRE Contents
'Click First Cell Filtered
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 16).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.ShowAllData
  
'Change Column Q to Current Month 2021
 Range("S1").Select
    ActiveCell.FormulaR1C1 = "B-VALUE " & MonthName(Month(Now)) & " 2021"

'Filter for Yellow
 ActiveSheet.Range("$A$1:$V$2474").AutoFilter Field:=17, Criteria1:=RGB(255 _
        , 255, 204), Operator:=xlFilterCellColor
  
'Change Formula and Select Active Cell
'Change formula to *8
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 17).Select
    Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "=+RC[-1]*7"

End Sub

 

[rlv01]

Which line of code does it crash or freeze on?

 

[Ordnassela]

Which line of code does it crash or freeze on?

It worked when I stepped through but once I pressed play Excel would freeze.

I figured it out though-simply kept ActiveSheet consistent throughout my code!

 

[Joe4]

Note that most of the time "Select" statements are unnecessary in VBA code, and you can speed up your code by combining lines that end in "Select" and the next line starts with "ActiveCell" or "Selection".

So this:

 Range("S1").Select
    ActiveCell.FormulaR1C1 = "B-VALUE " & MonthName(Month(Now)) & " 2021"

can be simplified to this:

 Range("S1") = "B-VALUE " & MonthName(Month(Now)) & " 2021"

 

[rlv01]

If you just use Cells(BooBoo, "BP").Value instead of say Activesheet.Cells(BooBoo, "BP").Value you are relying on implicit references and relying on implicit references can get you into trouble if you are adding code snippets into other functions. For this reason it is good practice to get into the habit of alway using explicit references even if what you intend is to use ActiveSheet.

 

[Ordnassela]

Note that most of the time "Select" statements are unnecessary in VBA code, and you can speed up your code by combining lines that end in "Select" and the next line starts with "ActiveCell" or "Selection".

So this:

 Range("S1").Select
    ActiveCell.FormulaR1C1 = "B-VALUE " & MonthName(Month(Now)) & " 2021"

can be simplified to this:

 Range("S1") = "B-VALUE " & MonthName(Month(Now)) & " 2021"

Noted! Thanks for this. I'll tweak my code