CODEWITHSUNDEEP
vbaexcel
baarkerlounger
baarkerlounger

Reputation: 1226

Using variable in VLOOKUP

I'm trying to use VLOOKUP to check if my variable occurs in a listobject and if so retrieve the corresponding value.

My code always goes to error at the following line though:

shtData.Cells(rowNumber, perf1).Value = wsFunc.IfError(wsFunc1.VLookup(shtData.Cells(rowNumber, cep1).Value, perfTable, 2, False), Null)

My code is below:

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsFunc As WorksheetFunction
Set wsFunc = Application.WorksheetFunction
Dim wsFunc1 As WorksheetFunction
Set wsFunc1 = Application.WorksheetFunction
Dim cep As Variant
Dim perfTable As ListObject
Set perfTable = perfData.ListObjects("PerfTable")
Dim cepTable As ListObject
Set cepTable = perfData.ListObjects("cepTable")

perfCol = perfTable.ListColumns("Column1").Index
perfSort = perfTable.ListColumns("Column2").Index
cepCol = cepTable.ListColumns("Column1").Index
cepSort = cepTable.ListColumns("Column2").Index

On Error GoTo errHandler

Set usedRange = returnUsedRange(shtData, wb)
Set perfRange = returnUsedRange(perfData, wb)
Set cepRange = returnUsedRange(perfData, wb)

 For Each Row In usedRange
    For Each sec In secRange
        rowNumber = Row.Row
        secRow = sec.Row
            shtData.Cells(rowNumber, perf1).Value = wsFunc.IfError(wsFunc1.VLookup(shtData.Cells(rowNumber, cep1).Value, perfTable, 2, False), Null)
    Next

Upvotes: 0

Views: 208

Answers (2)

MLDev
MLDev

Reputation: 1277

Why not use this method. Its fast and easy to implement:

Dim i As Integer
Dim arrData2Search() As Variant

Set arrData2Search = Range(Cells(1, 1), Cells(1000, 2000)).value


    For i = 1 To 2000
        If arrData2Search (i, 1)= "Target" Then
             ' your code
        End If
    Next i

Upvotes: 0

CRondao
CRondao

Reputation: 1903

You can not use excel Iferror function like that:

Just use

   x = application.vlookup(...,...,...,...)

without the WorksheetFunction so you can check Vlookup for errors...

   if iserror(X) then ' vlookup failed (x is variant)

Upvotes: 1

Related Questions