Listbox not displaying proper vlookup result

Question

I am trying to execute this part of the code.

Do While ws2.Cells(i, "A").Value <> Empty
    arr2(k, j) = ws2.Cells(i, "A").Value
    arr2(k, j + 1) = Application.VLookup(ws2.Cells(i, "A").Value, ws.Range("B:K"), 2, False)
    formula = ("VLOOKUP($A" & i & "&" & User.TextBox1.Value & ", CHOOSE({1,2}, 'DATA'!$B$4:$B$7669&'DATA'!$D$4:$D$7669,'DATA'!$E$4:$E$7669),2,0)")
    q = CStr(Evaluate(formula))
    arr2(k, j + 2) = q
    k = k + 1
    i = i + 1
Loop

I don't know why, but I get different results at times, without making any changes. Sometimes it shows error for the vlookup value but sometimes it shows correct number. What can I do to fix this problem?


Show source
| excel-vba   2017-08-08 21:08 1 Answers

Answers to Listbox not displaying proper vlookup result ( 1 )

  1. 2017-08-08 21:08

    You are not specifying on which sheet the evaluate should run.

    So the $A" & i & .. is looking on the active sheet.

    Change

    q = CStr(Evaluate(formula))
    

    to

    q = CStr(ws2.Evaluate(formula))
    

Leave a reply to - Listbox not displaying proper vlookup result

◀ Go back