Selected Answer
I wouldn't use HLOOKUP on principle, the principle being that I use worksheetfunctions on worksheets and VBA functions in VBA. Whenever you want to use worksheetfunctions in VBA use the type adapted to VBA, meaning Application.Hlookup() rather than Applciation.Worksheetfunction.Hlookup(). But when it comes to HLOOKUP I wouldn't use it at all because it is harder to set up in VBA (otherwise you wouldn't have a question) and harder to trap errors. I would use code like this:-
Sub MyCalender()
Dim Cont(1) As String
Dim LookFor As Date
Dim Fnd As Range
LookFor = Sheet1.Cells(40, "D").Value 'CLng(Sheet1.Range("D40"))
' you might modify the LookFor value like this:
' LookFor = CDate(Format(.Cells(40, "D").Value, "dd/mm/yyyy"))
With Sheet2.Range("B2:AK2")
Set Fnd = .Find(What:=LookFor, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchByte:=False)
End With
If Fnd Is Nothing Then
MsgBox "The date " & Format(LookFor, "dd/mm/yyyy") & _
" wasn't found.", vbInformation, "Invalid date"
Else
Cont(0) = Sheet1.Cells(3, "B").Value
Cont(1) = Sheet2.Cells(1, Fnd.Column).Value
Sheet1.Cells(6, "D").Value = Join(Cont, " ") ' = .Range("D6").Value = Fnd.Value
End If
End Sub
First you declare what you look for. That would be the Date "LookFor".
Then you declare where to look. I did this in the With statement.
The Find method will return a range object. That was declard at the top and is now used.
The key to this code is in the date format. The format in D40 must be exactly the same as in B2:AK2. I tried
LookFor = CDate(Format(.Cells(40, "D").Value, "dd/mm/yyyy"))
, meaning if D40 has a different format you can adapt it to the format in the search range.
Excel will start the search in the cell after the specified one, search tot he end of the range and loop back to the beginning (as determined by SearchDirection). Therefore the code specifies the last cell to start with, which means it will start with cell 1.
You are looking at values (not formulas) and the whole cell content (not some part of it). HLOOKUP doesn't offer you a control panel like that.
Now, if no match is found the Fnd range will be empty and an error message can be issued. Else Fnd specifies a range which has all the properties of a range, including Value, Row and Column. HLOOKUP would use the Column property and then find a cell at an offset to Fnd. You don't need that because you want the found cell itself.
I kept traces of your original code in the above to help you find your bearings.
