VBA Listbox & Lookup

user_16234727 0
Viewed 295

Updated and Edited

I am new to this whole world, but here is my issue as it stands: As the userform initializes the below code applies a filter to my 'clean import', copies column a into a temp sheet, which is what the listbox uses to populate itself.

     Set ws = ThisWorkbook.Worksheets("Clean_Import")
  
  ws.Activate
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0
  ws.Range("A1:K1000").AutoFilter Field:=5, Criteria1:="<1"
 
    Range("A:A").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Sheets("TempSheet").Select
    Columns("A:A").Select
    Range("A2").Activate
    ActiveSheet.Paste

ODList1.List = Sheets("TempSheet").Range("A2:A100").Value


End Sub

From then in it is just double clicking on the list box to lookup the selected items and vlook some data into text boxes.

  With Me.ODList1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
      
        TextBox11.Value = Application.VLookup(.List(i, 0), Sheet3.Range("A1:K100"), 3)
        TextBox12.Value = Format(Application.VLookup(.List(i, 0), Sheet3.Range("A1:K100"), 7), "dd / mm / yyyy")
        TextBox13.Value = Application.VLookup(.List(i, 0), Sheet3.Range("A1:K100"), 10)
       
        Exit For
      End If
    Next
  End With

This code works in all but one of my scenarios. I realise now if i select the list box item that happens to be the first line in the range OR the last in the range in my 'clean import' then I get the following error.

Run-Time error '-2147352571 (80020005)': Could not set the value property. Type mismatch.

The only thing I can think is that the value doesn't match, but that doesn't seem possible as the list box is populated from a direct copy from the range it is vlooking through

I look forward to hearing your thoughts,

Cheers,

Bill

vlookup userform vba excel listbox
2 Answers

That is happening because the Vlookup is not able to find a match. Here is a simple way to reproduce the error

Private Sub CommandButton1_Click()
    TextBox1.Value = Application.VLookup("Sid", Sheet1.Range("A1:K100"), 3)
End Sub

enter image description here

To handle this, you need to introduce proper error handling. Here is an example

Dim Ret As Variant
Ret = Application.VLookup("Sid", Sheet1.Range("A1:K100"), 3)

If IsError(Ret) Then
    TextBox1.Value = "Error"
Else
    TextBox1.Value = Ret
End If

enter image description here

user_11405790

I made some small adjustments to the way I brought the data in and problem went away.

Not really sure where the issue was creeping in. But it crept back out again.

user_162347270
Related