Matching dates WorksheetFunction

user_19578207 0
Viewed 21

I currently have the following VBA Code:

Sub SearchProjects()

Dim Ws As Worksheet
Dim NewSheet As Worksheet
Set NewSheet = Worksheets("Sheet1")

 Dim Rng As Range, rng2 As Range
 Dim myCell As Object
 Dim cell2 As Object
 Dim proj As String, d As Date
 Dim m As Variant
 
 Set Ws = Worksheets("Project tasks")
 Ws.Activate
 
 Set Rng = Ws.Range("D:D")
 Set rng2 = Range("DatesByWeek").EntireRow 'row 4 of my "Sheet1"
 
 searchString = "Lisa"
 For Each Cell In Rng
    If InStr(Cell, searchString) > 0 Then
        proj = Cells(Cell.Row, Range("ProjectName").Column)
        'so here d is a date found that corresponds to Lisa's project name, e.g. d = "25/07/2022"
        d = Format(Cells(Cell.Row, Range("StartDate").Column), "dd/mm/yyyy")
        m = WorksheetFunction.Match(d, rng2, 1) 'Searches Row 4 for any matches to the date d
        msgbox(m)
    End If
    
Next Cell
    
End Sub

When I do in normal excel function =MATCH("25/07/2022", 4:4, 1) it does return the correct column number, however the vba code continues to get the error:

Unable to get the match property of the WorksheetFunction class.

I'm not sure why it is an error in VBA? Any help appreciated

match excel-formula vba excel
1 Answers

Maybe you could try: (Just from the top of my head, have not been able to replicate the error)

Dim res As Variant
res  = WorksheetFunction.Match(d, rng2, 1) 
If Not IsError(res) Then
    Msgbox res  
End If
user_23101540
Related