DateAdd Function Question

[rodrigo_m_almeida]

Good Morning,

I need to subtract 1 day for date that time is less than 02:59:59

Public Sub BradPitt()
    With ActiveSheet
            .AutoFilterMode = False
                With Range("D1", Range("D" & Rows.Count).End(xlUp))
                    .AutoFilter 1, "<02:59:59"
                    On Error Resume Next
                    .Columns(-2).Offset(1).SpecialCells(12).Value = DateAdd("d", -1, ActiveCell) ' PROBLEM <
                End With
            .AutoFilterMode = False
        End With
End Sub

Using "ActiveCell" in DateAdd, no subtract anything from the found cell

I'm trying to do this with autofilter because my sheet is too big...

 

[BSALV]

Public Sub BradPitt()
     With ActiveSheet
          Set c1 = .Range("AA1")                                'somewhere an auxiliary cell
          c1.Value = -1                                         'value you want to substract

          .AutoFilterMode = False
          With Range("D1", Range("D" & Rows.Count).End(xlUp))
               Set c = .Offset(1, -2).Resize(.Rows.Count - 1)   'Column B without the header
               .AutoFilter 1, "<02:59:59"
               If .SpecialCells(12).Count > 1 Then
                    c1.Copy                                     'that -1
                    c.SpecialCells(12).PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False     'substract if from previous value for all visible cells
               End If
          End With
          .AutoFilterMode = False
     End With
End Sub