Run-time error ' 1004': Application-defined or object defined error

[dversloot1]

I'm trying to get the ROW number from a specific sheet where a cell value has been found.
Cnt = 5
ID = 479
Row = 0 --> This should return 19 but for some reason it is returning a 0.

My code is:

Private Sub CommandButton1_Click()
Dim ID As Variant
Dim Row As Long
Dim Cnt As Long
Dim Analyst As String

Cnt = InStr(1, ListBox2.Value, "-")
ID = Trim(Left(ListBox2.Value, Cnt - 1))
On Error Resume Next
Row = Application.WorksheetFunction.Match(ID, Sheets("Data").Range("B1:B2000"), 0)
On Error GoTo 0

Analyst = "A" & Row

Sheets("Sheet1").Range("B4").Value = Sheets("Data").Range(Analyst).Value


End Sub

Anyone know why this is the case?

 

[Jonmo1]

Trim and Left functions return TEXT strings, even if they are numeric characters.

Try changing
ID = Trim(Left(ListBox2.Value, Cnt - 1))
to
ID = Val(Trim(Left(ListBox2.Value, Cnt - 1)))

 

[dversloot1]

That did the trick!

Thanks a lot Jonmo1

 

[Jonmo1]

You're welcome

 

[dversloot1]

The VAL() doesn't seem to work when the ID is alphanumeric.
Thoughts?

 

[Jonmo1]

Inconsistent data is the root of all evil in Excel.

So sometimes the ID will be a number, and sometimes it will be a Text String ?

Try
ID = Trim(Left(ListBox2.Value, Cnt - 1))
If IsNumeric(ID) Then ID = Val(ID)

 

[dversloot1]

Luckily there was a way around this inconsistent data.

Thanks again! Your solution worked.