locked
Vlookup in VBA RRS feed

  • Question

  • 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!

     

    Monday, September 17, 2007 7:22 PM

All replies

  • 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.

    Wednesday, September 19, 2007 3:17 AM
  • Awesome thanks. I will give it a shot tomorrow and let you know how it goes.
    Wednesday, September 19, 2007 3:34 AM
  • Did you manage to get this one to work?

     

    Tuesday, October 2, 2007 2:58 AM