VBA to find a date in a range

[BillDoor]

Hi all

I have the below code, which I have used in other sheets, but I can not seem to get it to work in the attached, it's driving me a little nuts, can some one please point out whatever stupid mistake I am making!


All I want it to do is find the cell in the row 3 that matches the date in C1
thanks

Sub test()
        
        Dim MonthCopy As Variant
        
        MonthCopy = Range(Trim("C1")).Value
        
        Rows("3:3").Select
        Selection.Find(what:=MonthCopy, LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
        
        
End Sub

[NeedForExcel]

Try this

Sub test()
    Cells(3, Application.WorksheetFunction.Match(Range(Trim("C1")).Value2, Range("A3:DC3").Value2, 0)).Activate
End Sub

You can infact get rid of the Trim if you really want and just have this

Sub test()
    Cells(3, Application.WorksheetFunction.Match(Range("C1").Value2, Range("A3:DC3").Value2, 0)).Activate
End Sub

Ofcourse, make sure all Dates are actual dates and not text strings looking like dates.

[vcoolio]

Hello BillDoor;

Try the following code:-

Sub test()

    Dim c As Range
    
        For Each c In Range("AF3:BB3")
        If c.Value = Range("C1").Value Then c.Select
    Next
     
End Sub

The code will simply select the date in row 3 that matches the cell C1 date.

Cheerio,
vcoolio.

[BillDoor]

thanks all, that's worked fine for me