I am writing a macro to go thru a column of data and do a vlookup on another page for the value and return that value into the specified cell. The data looks like this and I need to use the ~ as a delimeter (if present) and process each value in my Vlookup as its own value to see if I find an exact match:
I used the split function to split it at the ~ and then try to loop thru the data to find matches. If there is only one value in the cell the code runs but when it hits the one with 3 entries I get the ERROR "Unable to get the Vlookup Property of the WorksheetFunction Class".
Any help is appreciated!!
Dim LookItUp, F5Value As String
Set SGPSRange = Worksheets("SGPSUSERS").Range("A:H")
For i = 2 To F5RowCount 'for every value on the Data sheet
OriginalUsername = Cells(i, 19).Value 'Grab the cell information to be checked
ParsedValue = Split(OriginalUsername, "~") 'split up into an array for processing using ~ as delimeter
For Each CheckValue In ParsedValue 'go thru each value in the split array
LookItUp = WorksheetFunction.VLookup (CheckValue, SGPSRange, 1, False)
If Not IsEmpty(LookItUp) Then 'if it was not #NA
FullInfo = WorksheetFunction.VLookup(CheckValue, SGPSRange, 8, False) 'match was found so
If CheckValue = LookItUp Then 'write out the data '
Cells(i, 20).Value = LookItUp
Cells(i, 21).Value = FullInfo
End If 'end is equal means value found
End If 'end is not #NA
LookItUp = "" 'clear the values
FullInfo = ""
Next CheckValue ' go to next value in Parsed Value if the split array had more than one value to see if you find a match