Please help, this code is working fine except for one line.

[Alphaboss7]

Hi there,

The code below has been working fine for dates before 10/1/2019 and when the B22 cell has the value of "This Month", but whenever the B22 cell has a future date (11/1/19, 12/1/19, etc.) then the code stops working on the first line of the first For Loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim table As Range
    Dim list As Range
    Dim Sht As String
    Dim i As Long
    Dim j As Long
    
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address = Range("Team_DrpDn").Address And Range("Users_DrpDn").Value = "All" Then
        Select Case Target.Value
            Case "SysAdmin"
                Set table = Sheets("Forecast Archive").Range("A1:E10000")
                Set list = Sheets("Forecast Archive").Range("A2:A10000")
                Sht = "SysAd Historical"
            Case "SecOps"
                Set table = Sheets("Forecast Archive").Range("G1:K10000")
                Set list = Sheets("Forecast Archive").Range("G2:G10000")
                Sht = "SecOps Historical"
            Case "CyberSecurity"
                Set table = Sheets("Forecast Archive").Range("M1:Q10000")
                Set list = Sheets("Forecast Archive").Range("M2:M10000")
                Sht = "CyberSecurity Historical"
            Case "Network"
                Set table = Sheets("Forecast Archive").Range("S1:W10000")
                Set list = Sheets("Forecast Archive").Range("S2:S10000")
                Sht = "Network Historical"
            Case "DBA"
                Set table = Sheets("Forecast Archive").Range("Y1:AC10000")
                Set list = Sheets("Forecast Archive").Range("Y2:Y10000")
                Sht = "DBA Historical"
        End Select
        
        If Range("B22").Value >= DateSerial(2019, 10, 1) Or Range("B22").Value = "This Month" Then
        For i = 27 To 30
            Range("G" & i).Value = Application.WorksheetFunction.HLookup(Range("E" & i).Value, table, Application.WorksheetFunction.Match(Range("B22").Value, list, 0) + 1, False)
            Range("M" & i).Value = Application.VLookup(Range("J" & i).Value, Sheets(Sht).Range("J10:M13"), 4, False)
        Next i
        Else
        For j = 27 To 30
            Range("G" & j).ClearContents
        Next j
        End If
    End If
    
End Sub

I'm not sure why the logic isn't working but the program stops at that point. Any help will be much appreciated.

Thanks,

AB7

 

[Alphaboss7]

That worked! Thanks, yky, I appreciate the help!

 

[Alphaboss7]

So yky's response actually solved the issue. But to answer your question, no, I was only getting the error when B22 had a date in the future. Past dates were working just fine, but it seems Range("B22").Value2 was the solution.

Thanks for your help investigating.