Find Corresponding Date in Sheet Based on Corresponding Date in Sheet1("A5")

[OilEconomist]

Thanks in advance and I will post feedback for proposed solutions.

Here is my code thus far.

Sub FindDate2()
        
        Dim DT As Date, VR As Variant, CL As Long
        
        DT = Sheets("Sheet1").CDate(Cells(5, 1))
                   
        Set VR = Sheets("Sheets").Range("3:3").Find(DT, , , xlWhole)
        
        If Not DT Is Nothing Then
            CL = DT.Column
        
        Else
        
        Exit Sub
    
        End If
    
End Sub

For the date located in cell A5 of sheet "Sheet1", I am trying to find the corresponding column number/cell address within Row 3 of "Sheet2" with that same date.

The date in cell A5 of "Sheet1" is a hard coded value with the format "4/1/2019".

The dates in Row 3 of "Sheet2" are formulas after the first date entered.

 

[DanteAmor]

The date formats on both sheets must be mm / dd / yyyy
If on sheet1, in cell A5 you have a date, then we simply take that value.

Try

Sub FindDate2()
    Dim DT As Date, VR As Range, CL As Long
    
[COLOR=#0000ff]    DT = Sheets("Sheet1").Cells(5, 1).Value[/COLOR]
    Set VR = Sheets("Sheet2").Range("3:3").Find(DT, LookIn:=xlValues, lookat:=xlWhole)
    If Not VR Is Nothing Then
        CL = VR.Column
        MsgBox "Column :  " & CL
    Else
        MsgBox "Not found"
    End If
End Sub

If you have a text in cell A5 then you have to convert the text to date:
DT = cdate(Sheets("Sheet1").Cells(5, 1).Value)

 

[OilEconomist]

Thank you DanteAmor! That worked perfectly!

 

[DanteAmor]

You're welcome and thanks for the feedback.

 

[OilEconomist]

Hi DanteAmor I have further questions on this as I was trying to use it for dates posted horizontally. I did not post here because I could not find the link to this thread. I finally did once I did an exhaustive e-mail search.

Here's a link to the post: https://www.mrexcel.com/forum/excel-questions/1096249-vba-code-not-working-when-matching-date.html