Why am I getting a type mismatch on certain items on a listbox but not others?

[DrCheese1023]

Hey all! First off, here is my code:

Private Sub PopoutLeadListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim selectedItem As String
selectedItem = Me.PopoutLeadListBox.Column(11)

SalesForm.BHSDTAPNAMELF.Value = Application.XLookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("T:T"))
SalesForm.BHSDCOMPANYNAMELF.Value = Application.XLookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("U:U"))
End Sub

This is working great on a few of the items in my list box. However, most of them give me a type mismatch error, and the fact that it's not working on some items but is on others with no discernible difference between the items is confusing.

Am I missing something simple?

 

[aRandomHelper]

Val(selectedItem)

I am guessing this is giving you the error, if any of your selected item contains text, it can't be converted to a number.

 

[DrCheese1023]

I am guessing this is giving you the error, if any of your selected item contains text, it can't be converted to a number

Unfortunately they are all numbers and formatted the exact same, same amount of digits too.

 

[DanteAmor]

Let's check if they are numbers.

Try and tell us what the message says.

Private Sub PopoutLeadListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim selectedItem As String
  selectedItem = Me.PopoutLeadListBox.Column(11)
 
  If IsNumeric(selectedItem) Then
    SalesForm.BHSDTAPNAMELF.Value = Application.Lookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("T:T"))
    SalesForm.BHSDCOMPANYNAMELF.Value = Application.Lookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("U:U"))
  Else
    MsgBox "It is not numeric: " & selectedItem
  End If
End Sub

Or try this option:

Private Sub PopoutLeadListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim selectedItem As String
  Dim f As Range
 
  selectedItem = Me.PopoutLeadListBox.Column(11)
  With Worksheets("MASTER TAPS")
    Set f = .Range("S:S").Find(selectedItem, , xlValues, xlWhole)
    If Not f Is Nothing Then
      SalesForm.BHSDTAPNAMELF.Value = .Range("T" & f.Row).Value
      SalesForm.BHSDCOMPANYNAMELF.Value = .Range("U" & f.Row).Value
    Else
      MsgBox "Not found: " & selectedItem
    End If
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

​

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Why am I getting a Type Mismatch error when using some items from a listbox in a function?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DrCheese1023]

Let's check if they are numbers.

Try and tell us what the message says.

Private Sub PopoutLeadListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim selectedItem As String
  selectedItem = Me.PopoutLeadListBox.Column(11)
 
  If IsNumeric(selectedItem) Then
    SalesForm.BHSDTAPNAMELF.Value = Application.Lookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("T:T"))
    SalesForm.BHSDCOMPANYNAMELF.Value = Application.Lookup(Val(selectedItem), Worksheets("MASTER TAPS").Range("S:S"), Worksheets("MASTER TAPS").Range("U:U"))
  Else
    MsgBox "It is not numeric: " & selectedItem
  End If
End Sub

Or try this option:

Private Sub PopoutLeadListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim selectedItem As String
  Dim f As Range
 
  selectedItem = Me.PopoutLeadListBox.Column(11)
  With Worksheets("MASTER TAPS")
    Set f = .Range("S:S").Find(selectedItem, , xlValues, xlWhole)
    If Not f Is Nothing Then
      SalesForm.BHSDTAPNAMELF.Value = .Range("T" & f.Row).Value
      SalesForm.BHSDCOMPANYNAMELF.Value = .Range("U" & f.Row).Value
    Else
      MsgBox "Not found: " & selectedItem
    End If
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

​

My issue was actually super simple. Some of the data was missing from the "MASTER TAPS" sheet and was pulling a type mismatch error because of that. The code was actually perfect, the data wasn't. Thank you so much, you helped me a lot. I was pulling my hair out.