VLookup Not Working for Change Event Sub

Hi,

I am having trouble running a VLookup inside a Change Event sub. I have tested all other lines of code and made sure they work, so it's only the VLookup that's not working.

For brief background, I have two sheets. Sheet1 contains the ID (where it could have multiple IDs on separate line, hence the SPLIT function used below), Sheet 2 contains the ID and its Description. What I wanted to do is perform a VLookup upon value change and insert description for each ID as comment to the cell.

The line that is not working for me is:
Application.WorksheetFunction.VLookup(IDs(i), Sheet2.Range("A3:B30"), 2, False)

I'm not getting any errors but it just jumps right out without continuing. I'm certain that the ID exists in the table for the VLookup. If someone can help me point out why it is not working, I will be very appreciated!

Below is a snippet of the code where VLookup is used:

With Target
        If .Comment Is Nothing Then
            'do nothing
        Else
            .Comment.Delete
        End If
        
        If Target.Value = "" Then
            .Comment.Delete
        Else
            If InStr(Target.Value, vbCrLf) = 0 Then
                IDs = Split(Target.Value)
            Else
                IDs = Split(Target.Value, vbCrLf)
            End If
            
            For i = LBound(IDs) To UBound(IDs)
                If commentText = "" Then
                'Add description for ID as comment
                commentText = Application.WorksheetFunction.VLookup(IDs(i), Sheet2.Range("A3:B30"), 2, False)


                Else
                'Keep on adding description for each ID as comment
                commentText = commentText & vbCrLf & Application.WorksheetFunction.VLookup(IDs(i), Sheet2.Range("A3:B30"), 2, False)
                End If
            Next




            .AddComment Text:=commentText
            .Comment.Shape.TextFrame.AutoSize = True
        End If
    End With

Re: VLookup Not Working for Change Event Sub

Quote from norie;798727

Is the line of code actually being executed?

Hi Norie,

Thank you for the quick reply.

I put a breakpoint at the line and it gets there, but when I press F8, it jumps right to ExitHandler and skips the rest of the code.

Re: VLookup Not Working for Change Event Sub

Quote from norie;798727

Is the line of code actually being executed?

Hi Norie,

Some additional info, this is the code I'm currently running. So the program runs fine through the For loop, the cell comment gets even updated with the "Hello" text. I put a breakpoint at testO and wants to see what testO stores after the VLookup is ran, but it seems to store blank (""), and jumps right to exitHandler when I do F8, ignoring the rest of the logic I had.

For i = LBound(IDs) To UBound(IDs)
                If commentText = "" Then
                commentText = "Hello" & i
                Else
                commentText = commentText & vbCrLf & "Hello" & i
                End If
                testO = Application.WorksheetFunction.VLookup(IDs(i), Sheet.Range("A21:B31"), 2, False)
            Next

Re: VLookup Not Working for Change Event Sub

Quote from mikerickson;798776

If IDs(i) is not there, then the code will error.

Try using Application.VLookup rather than Application.WorksheetFunction.VLookup.

Also commentText should be data type Variant, so it can become the error value that will be returned if IDs(i) is not found.

Hi Mike,

Thank you so much!

I am able to get the code working by changing the Vlookup to:

Application.VLookup(CLng(IDs(i)), Sheet2.Range("A3:B30"), 2, False)

I'm not sure why the CLng is needed but without it I get the 2042 error.

Thank you again!!

Oliver