mnptl
Although VLOOKUP and LOOKUP do appear in the Help information in Excel, I have never managed to get them to work from VBA. Instead, when I need to do the type of lookup that you're trying out here, I have to use the .FIND command to hunt for my value in one column, and then grab the value I need from the next column. To make matters worse, I also need to add an error handling routine. This is because the .FIND action generates error 91 if it does NOT find the data I'm looking for.
That was all rather long to put into words. If you change your code to use .FIND, it may end up looking like this - give it a shot.
'*********************************************************************************************************
Sub proScoreCard1()
On Error GoTo Err_proScoreCard1 ' Error trapping command.
Dim rng As Range
Dim tradeDate As Date
Dim confirmDate As Date
Dim t1mDate As Variant
Dim nmRow As Double ' Store the number of the row where we find TRADEDATE.
Set rng = Range("H2")
tradeDate = rng.Value
confirmDate = rng.Offset(0, 4).Value
Do Until IsEmpty(rng)
' Step 1: Go down the first column, looking for the value we need.
With Worksheets("Sheet1").Range("A2:A62")
Set vrFound = .Find(tradeDate)
nmRow = vrFound.Row ' If it's not found, error 91 will be generated at this point.
End With
t1mDate = Cells(nmRow, "E") ' This is the cell (row & column) where we find T1MDATE.
' If we got here, then the value was found, so we need to do some more work.
If nmRow <> 0 Then
If confirmDate <= t1mDate Then
rng.Offset(0, 5) = "X"
Else
rng.Offset(0, 5) = ""
End If
Set rng = rng.Offset(1, 0)
End If
Loop
' New error handling section added - to cater for error 91.
Exit_proScoreCard1:
Exit Sub
Err_proScoreCard1:
Select Case Err.Number
Case 91 ' This happens if we can't find the value we want.
Resume Next
End Select
Resume Exit_proScoreCard1 ' If we got here, then we don't know what the hassle is - so
' it may be better to just leave!
End Sub
'*********************************************************************************************************
Let us know how it goes.