crainsworth


Hey eveyone,

I'm have a lot of difficulty using a vlookup in a macro I'm creating. If anyone could help it would be greatly appreciated.

Here is the code:

'**********************************************************************

Sub proScoreCard1()

Dim rng As Range
Dim tradeDate As Date
Dim confirmDate As Date
Dim t1mDate As Date

Set rng = Range("H2")
tradeDate = rng.Value
confirmDate = rng.Offset(0, 4).Value

Do Until IsEmpty(rng)

t1mDate = Application.VLookup(tradeDate, _
Worksheets("Lookup").Range("A2:E62"), 3, False)

If confirmDate <= t1mDate Then
rng.Offset(0, 5) = "X"
Else
rng.Offset(0, 5) = ""
End If

Set rng = rng.Offset(1, 0)

Loop

End Sub

"***************************************************************************

The error I am receiving is "Runtime error 13: Type Mismatch"

Thanks in advance!




Re: Vlookup in VBA

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.






Re: Vlookup in VBA

crainsworth

Awesome thanks. I will give it a shot tomorrow and let you know how it goes.





Re: Vlookup in VBA

mnptl

Did you manage to get this one to work