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