CODEWITHSUNDEEP
excelvba
MsAgentM
MsAgentM

Reputation: 153

Variable not being assigned in vlookup

I have a table I am trying to use to do a vlookup in column2 of the table and get data from column8 in the table. However, the variable will not be assigned. When the code gets to the variable indicated, proceed without assigning a variable and then skip the if statement altogether. I don't get an error, the code just proceeds as though its not there. Can someone tell me why this variable isn't being assigned data from the vlookup

Option Explicit
Dim RevSID As String
Dim RevSupLev As String
Dim RevActive As String
Dim DueDate As Date



Private Sub Contact_Update()
Set CaseRng = CaseRevPvt.DataBodyRange *Another pivot table in the workbook
Set Contact = Worksheets("Tables").ListObjects("Contact") 

 For Each cell In CaseRng

    RevSID = cell.Offset(0, 1)
    RevSupLev = cell.Offset(0, 2)
    RevActive = cell.Offset(0, 3)

    If RevSID = 0 Then 'An integer variable function doesn't need to run if no data
        On Error Resume Next
        End If  

    elseif RevActive = "No" then
         'Do stuff..works fine
    elseif RevSupLev = "String indicated" then
        if PADate>duedate then 'checks PADue for condition
             'does stuff, this works
        else: Call StandRev 'the intent is to do a Vlookup using RevSID,
                'find the matching data in Column2 of the Contact table and assign the
                information in Column8 to lastrev

Private Sub StandRev()
Dim VlookUp As Range
Dim lastrev As Date


With Worksheets("Tables") 'sets a look up range within the table "Contact"
Set VlookUp = Contact.Parent.Range(Contact.ListColumns("SID").DataBodyRange, Contact.ListColumns("Last Review").DataBodyRange)

lastrev = Application.WorksheetFunction.VlookUp(RevSID, VlookUp, 8,False)  '*** problem here -- RevSID variable is assigned in previous sub
' no data is saved in variable, program ends sub

If lastrev > AttempDate2 Then
    'code that will replace lastrev with data in AttempDate2, AttempDate2 varaiable assigned in another sub
End If

End With

End Sub

Upvotes: 0

Views: 55

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

If RevSID wasn't declared explicitly, then the RevSID in procedure1 would not be the same variable as RevSID in procedure2: undeclared variables are always locally scoped, so assigning it in procedure2 wouldn't affect the value of the same-name variable in procedure1.

But that's not what's going on here. Since RevSID is declared somewhere, your lookup must be failing (i.e. it's not finding the RevSID value in the lookup table).

I'll suggest a wildly different approach, using a function instead, and a pattern known as the "try pattern", where you have a function that returns a Boolean and outputs a result in a parameter that's passed by reference, that only has a meaningful value when the function returns True - and since at a glance it appears that the [SID] column isn't the left-most in the table (why would you be going all the way up to Contact.Parent otherwise?), I'll suggest using a combination of INDEX and MATCH to perform the lookup - notice how the order of the columns becomes irrelevant with this lookup approach.

Here's a version with early-bound WorksheetFunction calls, which raise a run-time error upon failure:

Private Function TryGetRevisionDate(ByVal SID As String, ByRef outResult As Date) As Boolean
    On Error GoTo CleanFail

    With Application.WorksheetFunction
        Dim matchRow As Long
        matchRow = .Match(SID, Contact.ListColumns("SID").DataBodyRange, 0)

        Dim indexValue As Variant
        indexValue = .Index(Contact.ListColumns("Last Review").DataBodyRange, matchRow)
    End With

    If IsDate(indexValue) Then outResult = indexValue
    TryGetRevisionDate = True

CleanExit:
    Exit Function

CleanFail:
    'lookup failed
    Resume CleanExit
End Function

And a version with late-bound WorksheetFunction calls, which return an error value upon failure (note that you get no parameter info, and no compile-time validation with late-bound code, so watch out for typos - Option Explicit can't save you here):

Private Function TryGetRevisionDate(ByVal SID As String, ByRef outResult As Date) As Boolean
    With Application

        Dim matchRow As Variant
        matchRow = .Match(SID, Contact.ListColumns("SID").DataBodyRange, 0)
        If IsError(matchRow) Then Exit Function

        Dim indexValue As Variant
        indexValue = .Index(Contact.ListColumns("Last Review").DataBodyRange, matchRow)
        If IsError(indexValue) Then Exit Function

    End With

    If IsDate(indexValue) Then
        outResult = indexValue
        TryGetRevisionDate = True
    End If

End Function

Using either version, your calling code can now do this:

Dim revDate As Date
If TryGetRevisionDate(RevSID, revDate) Then
    MsgBox revDate
Else
    MsgBox "SID '" & RevSID & "' was not found."
End If

Upvotes: 1

Related Questions