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").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
       
    Next i
sierra810Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

         For Each CheckValue In ParsedValue              'go thru each value in the split array
             On Error Resume Next
             LookItUp = WorksheetFunction.VLookup(CheckValue, SGPSRange, 1, False)
             On Error GoTo 0
             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 = Empty  'clear the values
            FullInfo = Empty

         Next CheckValue 

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Use the .Find method when using VBA, with FINDNEXT if multiple values need returning

FIND Range Method

Attach an example workbook and I'll add the code if you want
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Another helpful approach is using CountIf to see if the value is present in the target range and then proceed for VlookUp of Match in VBA codes.

See if the following approach helps.....
For Each CheckValue In ParsedValue              'go thru each value in the split array
    If WorksheetFunction.CountIf(Worksheets("SGPSUSERS").Columns(1), CheckValue) > 0 Then
        LookItUp = WorksheetFunction.VLookup(CheckValue, SGPSRange, 1, False)
        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

Open in new window

Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.