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

 

[yky]

First of all, what do you mean by "stops working"? What do you really see or experience?

Is you future date (11/1/19, 12/1/19, etc.) really a date, not text? Copy and paste it by value. What do you see? If it's really a date, you should see a number, something like 43770.

 

[Fluff]

You need to change the size of your table, at the moment it's only 1 row, so you will always get an error.

 

[Alphaboss7]

Hi yky,

Yes the future date is really a date, in cell B22 it's literally 11/1/2019 in date format.

The error is:
Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class

I'm not sure what that means, is it simply that the code isn't registering my match function within the hlookup function?

 

[Alphaboss7]

Hi Fluff,

Thanks for responding. What do you mean my table is only 1 row? The range for the table variable in the code is always from one column row 1, to another column row 10000. This should be what I need for the hlookup function, but the list range is only one column because that's all I should need for the match function to work.

Can you show me where in the code my table is only 1 row if I'm mistaken?

Thanks,
AB7

 

[yky]

Yes the future date is really a date, in cell B22 it's literally 11/1/2019 in date format.

How do you know it's not text? Unless you do a copy and paste by value and see a number, I'm not convinced the data is in date format.

Because "This Month" is text, I highly suspect your "11/1/2019" is also text.

 

[Alphaboss7]

Ah, I see what you're saying now. Yes, I'm able to copy and paste by value getting 43770. I'm also able to use the code for dates before "This Month" (9/1/2019, 8/1/2019, etc.), so this is why I'm not sure why this isn't working.

 

[yky]

Instead of Range.value, use Range.value2.

 

[yky]

Range("B22").value would give something like "11/1/2019" while Range("B22").value2 would give 43770.

 

[Fluff]

What do you mean my table is only 1 row?

Oops, totally misread the code, the tables are fine.

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class

This error means that the date in B22 cannot be found in the relevant column.
Do you get the same error regardless of what the value of "Team_DrpDn" is?