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

Alphaboss7

New Member
Joined
Jul 31, 2017
Messages
28
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.

Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,718
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 31, 2017
Messages
28
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

New Member
Joined
Jul 31, 2017
Messages
28

ADVERTISEMENT

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

Well-known Member
Joined
Jun 7, 2011
Messages
1,718
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 31, 2017
Messages
28

ADVERTISEMENT

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

Well-known Member
Joined
Jun 7, 2011
Messages
1,718
Office Version
  1. 2010
Platform
  1. Windows
Instead of Range.value, use Range.value2.
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,718
Office Version
  1. 2010
Platform
  1. Windows
Range("B22").value would give something like "11/1/2019" while Range("B22").value2 would give 43770.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,523
Messages
5,523,370
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top