Application Match error 13

[willyman10]

I'm using Application.Match to get the row number of a value, and to fill a form with all data of the row. It works perfect when sought value is alphanumeric, but error 13 is returned if sought value is numeric. Why?


Error occurs on line
ultimafila = Application.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0) + 2


I've tried with
ultimafila = Application.Match(Me.textoCodigo.Value, ws.ListObjects("Riesgos").ListColumns(1).DataBodyRange, 0) + 2
but I got same error 13 with numeric values of textoCodigo

Using IsError() returns false if looked up value is alphanumeric, and returns true if looked up value is numeric. Both values I want to find exist in the range


Private Sub textoCodigo_Change()


Dim ws As Worksheet, ws2 As Worksheet, i As Double, j As Double, C As Range, ultimafila As Variant ', ultimafila As Double
Set ws = Worksheets("Identificación"): Set ws2 = Worksheets("lista_riesgos")
Set C = ws.ListObjects("Riesgos").ListColumns(1).DataBodyRange.Find(textoCodigo.Value, LookIn:=xlValues, lookat:=xlWhole)


If Trim(Me.textoCodigo.Value & vbNullString) = vbNullString Then
Me.textoCodigo = Null
Me.textoTipo = Null
Me.textoResponsable = Null
Me.textoDescripcion = Null
Me.txtDetalle = Null
Me.textoControles = Null
Me.textoFrecuencia = Null
Me.textoEscala = Null
Me.textoImpacto = Null
For j = 0 To listaObjetivos.ListCount - 1
listaObjetivos.Selected(j) = False
Next
Exit Sub
End If


ultimafila = Application.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0) + 2


If Trim(Me.textoCodigo.Value & vbNullString) = vbNullString Then
Exit Sub
End If
index = C.Row
textoCodigo.Value = UCase(textoCodigo.Value)
Me.alertaCodigo.Visible = False


Me.textoTipo = Application.WorksheetFunction.index(ws.Range("Riesgos[Tipo]"), Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.textoResponsable = Application.WorksheetFunction.index(ws.Range("Riesgos[Responsable]"), Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.textoDescripcion = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(4).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.txtDetalle = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(5).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))


If Trim(ws.ListObjects("Riesgos").DataBodyRange.Cells(ultimafila - 2, 25).Value & vbNullString) = vbNullString Then
Me.textoControles = Null
Me.textoFrecuencia = Null
Me.textoEscala = Null
Me.textoImpacto = Null
Else
Me.textoControles = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(21).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.textoFrecuencia = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(22).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.textoEscala = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(23).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
Me.textoImpacto = Application.WorksheetFunction.index(ws.ListObjects("Riesgos").ListColumns(24).DataBodyRange, Application.WorksheetFunction.Match(Me.textoCodigo.Value, ws.Range("Riesgos[Cod.]"), 0))
End If


For j = 0 To listaObjetivos.ListCount - 1
listaObjetivos.Selected(j) = False
Next


For i = 0 To listaObjetivos.ListCount - 1
If ws.Cells(index, (i) + 6) = "X" Then
listaObjetivos.Selected(i) = True
End If
Next


End Sub

[offthelip]

you could try using vba instead of using the excel functions code such as this:

( Note NOT TESTED)


Dim rng As RangeSet rng = ws.Range("Riesgos[Cod.]")
For Each cell In rng
If cell.Value = Me.textoCodigo.Value Then
ultimafila = cell.Row + 2
Exit For
End If

Next