Help with vlook in listbox

[RAires]

I have a listbox displaying the table in my form so you can see the changes you make there instead of having to go to the excel sheet.
I have the userform populate the boxes when i select a row in the listbox, the problem im running into is that some columns are not filled and that gives me a vlook error. is there a way to make it appear as blank when the cell is not filled?
this is the code i have, there 30 more rows but the code is the same

Private Sub lstDisplay_AfterUpdate()


Me.txtdepot.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 1, False)
Me.txtref_clients.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 2, False)
Me.txtproprietaire.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 3, False)
Me.txtadress.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 4, False)
Me.txtawb.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 5, False)
Me.txtmerchandise_sensible.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 8, False)
Me.txtdate_entreposage.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 10, False)
Me.txtentrepositaire_initial.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 11, False)
Me.txtrefclasseur.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 12, False)
Me.txtdouane_emetrice.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 13, False)
Me.txttransitaire.Value = Application.WorksheetFunction.VLookup(lstDisplay.Value, Range("B6:CV4985"), 14, False)

 

[Fluff]

One option would be

   Dim x As Variant
   x = Application.VLookup(lstDisplay.Value, Range("B6:CV4985"), 1, False)
   If Not IsError(x) Then Me.txtdepot.Value = x
   x = Application.VLookup(lstDisplay.Value, Range("B6:CV4985"), 2, False)
   If Not IsError(x) Then Me.txtref_clients.Value = x

 

[Fluff]

Another option would be like

   Dim x As Variant
   
   x = Application.Match(lstDisplay.Value, Range("B6:B4985"), 0)
   Me.txtdepot.Value = Cells(x, 2).Value
   Me.txtref_clients.Value = Cells(x, 3).Value

That way you only do the lookup once.

 

[Norie]

How are you populating the listbox?

If you are populating it from a table the the ListIndex of the selected row in the listbox should correspond directly to the row in the table so you wouldn't need formulas to get the values for the textboxes.

 

[RAires]

@Fluff its working perfectly now! thank you very much

@Norie not all columns have data for all items, i think that's why i was having trouble with the code i was using.

 

[Fluff]

Glad we could help & thanks for the feedback.