Condition to Subtract 1 Month

[JoeExcelHelp]

Im trying to modify the following code where the 'IF' condition subtracts 1 month
example,
If rgC.Value < Date Then
If rgC.Value < EDate(today(),-1) Then

Code: Select all

Sub CaptureHistoric()
Dim Sht As Worksheet
Dim rgC As Range
If MsgBox("Run the macro?", vbYesNo) = vbNo Then Exit Sub
Application.Calculation = xlCalculationManual
    For Each Sht In ActiveWorkbook.Worksheets
        Select Case Sht.Name
            Case "Programs", "Schedule", "Hours", "Attrition", "DataDates", "HoursR36", "Transfers", "ActualSAP", "ActualSAPTable", "RecruitingOrientation", "HiringPlan", "HiringForecast", "Data", "Data2", "Data3", "Data4"
                 'Ignore these sheets
            Case Else
                For Each rgC In Sht.Range("G11:CD11").Cells
                    If rgC.Value < Date Then
                        Sht.Range(Sht.Cells(52, rgC.Column), Sht.Cells(155, rgC.Column)).Value = _
                            Sht.Range(Sht.Cells(52, rgC.Column), Sht.Cells(155, rgC.Column)).Value
                    End If
                Next rgC
        End Select
    Next Sht
Application.Calculation = xlCalculationAutomatic
End Sub

[HansV]

Change Date to DateAdd("m", -1, Date)

See DateAdd function

[JoeExcelHelp]

Thanks Hans

I changed the first columns reference from (52, rgC.Column) to (52, 7)
Seems to work but I want to make certain you agree with the change

TY

Before

Sht.Range(Sht.Cells(52, rgC.Column), Sht.Cells(155, rgC.Column)).Value = _
Sht.Range(Sht.Cells(52, rgC.Column), Sht.Cells(155, rgC.Column)).Value

After

Sht.Range(Sht.Cells(52, 7), Sht.Cells(155, rgC.Column)).Value = _
Sht.Range(Sht.Cells(52, 7), Sht.Cells(155, rgC.Column)).Value

[HansV]

I don't know what you wanted to accomplish, but if it works for you, it should be OK.