CODEWITHSUNDEEP
excelvba
user3781528
user3781528

Reputation: 639

match values from sheet to array and return a corresponding value if a match is found

I need to lookup values from a sheet (the last 8 characters from column 1 & the content of column 6) in an array. If a match is found the corresponding value column 2 of the array needs to be populated into column 9 on the sheet. When I run my code there are no errors but nothing is returned into column 9.

Please help me fix the problem with my code. Thank you so much for your time. 

Sub LookupValues()

Dim LastRow, i As Long
Dim GeneCheck As String
Dim vArr As Variant
Dim x

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

vArr = Array(Array("HD300_QCL861Q", "5"), _
Array("HD300_QCE746_E749del", "5"), _
Array("HD300_QCL858R", "5"), _
Array("HD300_QCT790M", "5"), _
Array("HD300_QCG719S", "5"), _
Array("HD200_QCV600E", "10.5"), _
Array("HD200_QCD816V", "10"), _
Array("HD200_QCE746_E749del", "2"), _
Array("HD200_QCL858R", "3"), _
Array("HD200_QCT790M", "1"), _
Array("HD200_QCG719S", "24.5"), _
Array("HD200_QCG13D", "15"), _
Array("HD200_QCG12D", "6"), _
Array("HD200_QCQ61K", "12.5"), _
Array("HD200_QCH1047R", "17.5"), _
Array("HD200_QCE545K", "9"))



For i = 2 To LastRow

GeneCheck = Right(Cells(i, 1).Value, 8) & Cells(i, 6).Value

'//Tell VBA to ignore an error and continue (ie if it can't find the value)
        On Error Resume Next

'//Assign the result of your calculation to a variable that VBA can query

x = WorksheetFunction.VLookup(GeneCheck, vArr, 2, False)

     '//if Vlookup finds the value, then paste it into the required column
        If Err = 0 Then
Cells(i, 9).Value = Application.WorksheetFunction(GeneCheck, vArr, 2, False)
        End If

        '//resets to normal error handling
        On Error GoTo 0
Next


End Sub

Upvotes: 0

Views: 661

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Your code seems ok (I tried it quickly), except one error:

Cells(i, 9).Value = Application.WorksheetFunction(GeneCheck, vArr, 2, False)

You forgot to insert VLookup. But you dont need to redo again this lookup anyway, you already did it and have the value in x. So:

Cells(i, 9).Value = x

Upvotes: 1

Related Questions