I have in my userform listbox which display 10 columns. All rows have an individual ListIndex. I would like to display f.e. in Msgbox value after select some row and click button "Show". But this value is not avalible in listbox. So, I thought that all rows have individual ListIndex, I can use VLOOKUP for finding my target. For example: I selected row no. 1 then ListIndex is 1 and this is my Look up Value. In my database sheet I have the same individual ID values. Then of course, I have to declarate range, number of column and parameter "False". Theoretically, I expect the result after that but it doesn't work.
My code:
Dim indexno As Long 'this is my delcaration for finding Look up Value
Dim myVLookupResult As Long 'this is my declaration for VLookup Result
indexno = ListBoxResult.ListIndex 'my Lookup Value is dynamic and depend of selected row
myVLookupResult = Application.VLookup(indexno, Worksheets("DataBase").Range("A1:J100"), 5, False)
MsgBox myVLookupResult 'should display result of VLOOKUP
But the result is error: Run-Time error: 13 - Type mismatch. I guess the problem is with convert type of Dim from int to string.
Someone could support me, please? Thanks in advance!
The lookup value of a VLOOKUP worksheet function must be of the same data type as the data in which it is to be found. In your code the lookup value is of Long data type. If the column in which you are looking for it has text, the number you are looking for will not be found. So, you change the lookup value to Variant and hope that Excel will be able to work out what you want. But the better way is to examine your data column and look up the type of value you actually have there.
Next, given you are looking for a number which you have assigned to a Variant and Excel, in consequence, managed to find the string-equivalent of that number, that would be the functions return value, a text string. In most cases Excel is quite generous in this sort of situations but if it does complain about "Data Type" then it's because you are trying to assign a text string to a variable of Long data type.
Other than that, as @Michal Palko already pointed out, the ListBox's ListIndex is 0-based. If the numbers in your worksheet are 1-based the return of VLOOKUP won't be "totally different" but it will be from the adjacent row and therefore unexpected.
But I want to alert you to another possibility. As you know, you can load many columns into your list box. You can show some of the columns and hide others but you can access them all with code like this:-
With ListBox1
Debug.Print .List(.ListIndex, 3)
End With
This snippet will print out the value of the 3rd column in the row of ListIndex. You might also assign this value to a variable and, perhaps, have no need for VLOOKUP.