Ordnassela
New Member
- Joined
- Jun 2, 2021
- Messages
- 12
- Office Version
- 2013
- Platform
- Windows
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?
It does exactly what I need it to do, but when I press play, Excel freezes. Any thoughts on how I can fix it?
VBA Code:
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
Last edited by a moderator: