Hi experts,
Before adding values into column K, I need to delete the previous records. I have created a for loop to go through the col. K range to check whether the cells are empty if not clear the content, but not sure why is not working.
Code
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim rng As Range, cell As Range
Set rng = Range("K2", Range("K2").End(xlDown))
DaysToMRPDate = TextBox1.Value
If DaysToMRPDate = "" Then
Exit Sub
End If
'Selecting the correct sheet
Worksheets("MRPReqLive").Activate
'Filter all the Purchase Order <> 0
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5, Criteria1:="<>0", _
Operator:=xlAnd
'Looping through the column AddDayFactor to clear previous data records
For Each cell In rng
If IsEmpty(cell.Value) = False Then
cell.Value = 0
End If
Next cell
'Copy the value from the user form into the AddDayFactor
Range("K2", Range("K2").End(xlDown)).Value = TextBox1.Value
'Remove filter
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5
'Refreshing worksheet
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("MRPReqPivot").Activate
MsgBox ("Data is added successfully")
Call PivotMacro
Call ChangeCharts
Application.ScreenUpdating = True
End Sub