EXCEL VBA VLOOKUP HELP for ERROR "Unable to get the Vlookup Property of the WorksheetFunction Class"
Hi:
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:
Bannana
orange
Apple~AP001~Jon1
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").Ra
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(
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
Next i
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:
Bannana
orange
Apple~AP001~Jon1
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").Ra
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(
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
Next i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Martin Liss
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.