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.

I've tried using Using IsError(), it 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

 

[NARAYANK991]

Hi ,

The problem may be due to the data types involved.

A textbox always returns a String type value , irrespective of whether it is numeric or alpha.

A range , on the other hand , will automatically take alpha data as text and numeric data as numeric , unless the cells are formatted as Text , or the numeric values are entered by prefixing them with the apostrophe sign '.

Thus , when you do a match for an alphanumeric value (text string) , there is no data type incompatibility between the textbox value and the values within the range ; when a numeric value is being matched , the textbox value is a text value , whereas Excel has automatically taken the numeric values within the lookup range as numeric. The match therefore fails.