Error 13 using application.match

K

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I´m trying to populate fields coming from other cells, and I´m getting the error 13 when running the code.

VBA Code: 
Private Sub ComboBox2_Change()

If Me.ComboBox2.Value <> "" Then

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datos")
Dim i As Integer

i = Application.Match(VBA.CLng(Me.ComboBox2.Value)), sh.Range("A:A"), 0)


    Me.LastName.Value = sh.Range("B" & i).Value
    Me.ContactCompany.Value = sh.Range("C" & i).Value
    Me.ContactCountry.Value = sh.Range("D" & i).Value
    Me.ContactMail.Value = sh.Range("E" & i).Value
    Me.ContactPhone.Value = sh.Range("F" & i).Value
    Me.ContactCell.Value = sh.Range("G" & i).Value
    Me.ContactPosition.Value = sh.Range("H" & i).Value
    Me.ContactClass.Value = sh.Range("I" & i).Value

    If sh.Range("J" & i).Value = "Masculino" Then Me.MascButton.Value = True
    If sh.Range("J" & i).Value = "Femenino" Then Me.FemButton.Value = True


End If



End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Runtime error 13 is a type mismatch, so will typically occur if the content of the combobox can't be converted to a number by clng.

As you have declared i as Integer, it is a possibly that the value exceeds the limits of the Integer variable type, declaring i as Long would correct this.
 
Upvote 0
Jason,

Thank you for your answer, Now I´m getting the compilation error: syntax error, and the line below is marked in red.:

i = Application.Match(VBA.CLng(Me.ComboBox2.Value)), sh.Range("A:A"), 0)
 
Upvote 0
There should only be 1 closing parentheses after .Value, not 2.
i = Application.Match(VBA.CLng(Me.ComboBox2.Value), sh.Range("A:A"), 0)[/B]
The error was already there in post 1 but I missed it.
 
Upvote 0
That solved the syntax error but returned the error 13 types mismatch, I have two forms in this project, I´llpaste the complete code so you can take a look at it:


This is the code to "UpdateForm" Form:

VBA Code: 
Private Sub ComboBox2_Change()

If Me.ComboBox2.Value <> "" Then

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datos")
Dim i As Long

i = Application.Match(VBA.CLng(Me.ComboBox2.Value), sh.Range("A:A"), 0)


    Me.LastName.Value = sh.Range("B" & i).Value
    Me.ContactCompany.Value = sh.Range("C" & i).Value
    Me.ContactCountry.Value = sh.Range("D" & i).Value
    Me.ContactMail.Value = sh.Range("E" & i).Value
    Me.ContactPhone.Value = sh.Range("F" & i).Value
    Me.ContactCell.Value = sh.Range("G" & i).Value
    Me.ContactPosition.Value = sh.Range("H" & i).Value
    Me.ContactClass.Value = sh.Range("I" & i).Value

    If sh.Range("J" & i).Value = "Masculino" Then Me.MascButton.Value = True
    If sh.Range("J" & i).Value = "Femenino" Then Me.FemButton.Value = True


End If



End Sub

Private Sub CommandButton1_Click()

''''''''validacion''''''

If Me.ContactName.Value = "" Then
    MsgBox "Por favor, ingrese un nombre", vbCritical
    Exit Sub
End If

If Me.LastName.Value = "" Then
    MsgBox "Por favor,ingrese un apellido", vbCritical
    Exit Sub
End If

If Me.ContactCompany.Value = "" Then
    MsgBox "Por favor, ingrese una compañia", vbCritical
    Exit Sub
End If

If Me.ContactCountry.Value = "" Then
    MsgBox "Por favor,ingrese un país", vbCritical
    Exit Sub
End If

If Me.ContactMail.Value = "" Then
    MsgBox "Por favor, ingrese un correo", vbCritical
    Exit Sub
End If

If VBA.IsNumeric(Me.ContactCell.Value) = False Then
    MsgBox "Por favor, ingrese un celular", vbCritical
    Exit Sub
End If

If Me.FemButton.Value = False And Me.MascButton.Value = False Then
    MsgBox "Por favor, seleccione un género", vbCritical
    Exit Sub
End If

''''chequear duplicados'''''

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("datos")
Dim n As Long

If Application.WorksheetFunction.CountIf(sh.Range("E:E"), Me.ContactMail.Value) > 0 Then
    MsgBox "ESTE CONTACTO YA EXISTE EN LA BASE DE DATOS", vbCritical
    Exit Sub
End If

n = sh.Range("E" & Application.Rows.Count).End(xlUp).Row

sh.Unprotect "Master"

sh.Range("A" & n + 1).Value = Me.ContactName.Value
sh.Range("B" & n + 1).Value = Me.LastName.Value
sh.Range("C" & n + 1).Value = Me.ContactCompany.Value
sh.Range("D" & n + 1).Value = Me.ContactCountry.Value
sh.Range("E" & n + 1).Value = Me.ContactMail.Value
sh.Range("F" & n + 1).Value = Me.ContactPhone.Value
sh.Range("G" & n + 1).Value = Me.ContactCell.Value
sh.Range("H" & n + 1).Value = Me.ContactPosition.Value
sh.Range("I" & n + 1).Value = Me.ContactClass.Value

If Me.FemButton.Value = True Then sh.Range("J" & n + 1).Value = "Femenino"
If Me.MascButton.Value = True Then sh.Range("J" & n + 1).Value = "Masculino"

sh.Protect "Master"

Me.ContactName.Value = ""
Me.LastName.Value = ""
Me.ContactCompany.Value = ""
Me.ContactCountry.Value = ""
Me.ContactMail.Value = ""
Me.ContactPhone.Value = ""
Me.ContactCell.Value = ""


Me.ContactPosition.Value = ""
Me.ContactClass.Value = ""

Me.FemButton.Value = ""
Me.MascButton.Value = ""


MsgBox "Agregó un contacto con éxito"










End Sub

Private Sub CommandButton2_Click()
Me.ContactName.Value = ""
Me.LastName.Value = ""
Me.ContactCompany.Value = ""
Me.ContactCountry.Value = ""
Me.ContactMail.Value = ""
Me.ContactPhone.Value = ""
Me.ContactCell.Value = ""


Me.ContactPosition.Value = ""
Me.ContactClass.Value = ""

Me.FemButton.Value = ""
Me.MascButton.Value = ""




End Sub
Private Sub UserForm_Activate()

    With Me.ContactClass
    .Clear
    .AddItem ""
    .AddItem "Cliente Final"
    .AddItem "GSA"
    .AddItem "Distribuidor"
    .AddItem "Markem Imaje"
    .AddItem "Competencia"
       
    End With
   
    With Me.ContactPosition
    .Clear
    .AddItem ""
    .AddItem "Gerente"
    .AddItem "Técnico"
    .AddItem "Ventas"
    .AddItem "Comprador"
    .AddItem "Director"
    End With
   
 
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Datos")
Dim i As Long

Me.ComboBox2.Clear
Me.ComboBox2.AddItem ""

For i = 4 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox2.AddItem sh.Range("A" & i).Value

Next i

   
End Sub






And this is the code for the "AddContact" form:


Private Sub CommandButton1_Click()

If Me.ContactName.Value = "" Then
    MsgBox "Por favor, ingrese un nombre", vbCritical
    Exit Sub
End If

If Me.LastName.Value = "" Then
    MsgBox "Por favor,ingrese un apellido", vbCritical
    Exit Sub
End If

If Me.ContactCompany.Value = "" Then
    MsgBox "Por favor, ingrese una compañia", vbCritical
    Exit Sub
End If

If Me.ContactCountry.Value = "" Then
    MsgBox "Por favor,ingrese un país", vbCritical
    Exit Sub
End If

If Me.ContactMail.Value = "" Then
    MsgBox "Por favor, ingrese un correo", vbCritical
    Exit Sub
End If

If VBA.IsNumeric(Me.ContactCell.Value) = False Then
    MsgBox "Por favor, ingrese un celular", vbCritical
    Exit Sub
End If

If Me.FemButton.Value = False And Me.MascButton.Value = False Then
    MsgBox "Por favor, seleccione un género", vbCritical
    Exit Sub
End If

''''chequear duplicados'''''

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("datos")
Dim n As Long

If Application.WorksheetFunction.CountIf(sh.Range("E:E"), Me.ContactMail.Value) > 0 Then
    MsgBox "ESTE CONTACTO YA EXISTE EN LA BASE DE DATOS", vbCritical
    Exit Sub
End If

n = sh.Range("E" & Application.Rows.Count).End(xlUp).Row

sh.Unprotect "Master"

sh.Range("A" & n + 1).Value = Me.ContactName.Value
sh.Range("B" & n + 1).Value = Me.LastName.Value
sh.Range("C" & n + 1).Value = Me.ContactCompany.Value
sh.Range("D" & n + 1).Value = Me.ContactCountry.Value
sh.Range("E" & n + 1).Value = Me.ContactMail.Value
sh.Range("F" & n + 1).Value = Me.ContactPhone.Value
sh.Range("G" & n + 1).Value = Me.ContactCell.Value
sh.Range("H" & n + 1).Value = Me.ContactPosition.Value
sh.Range("I" & n + 1).Value = Me.ContactClass.Value

If Me.FemButton.Value = True Then sh.Range("J" & n + 1).Value = "Femenino"
If Me.MascButton.Value = True Then sh.Range("J" & n + 1).Value = "Masculino"

sh.Protect "Master"

Me.ContactName.Value = ""
Me.LastName.Value = ""
Me.ContactCompany.Value = ""
Me.ContactCountry.Value = ""
Me.ContactMail.Value = ""
Me.ContactPhone.Value = ""
Me.ContactCell.Value = ""


Me.ContactPosition.Value = ""
Me.ContactClass.Value = ""

Me.FemButton.Value = ""
Me.MascButton.Value = ""


MsgBox "Agregó un contacto con éxito"










End Sub

Private Sub CommandButton2_Click()
Me.ContactName.Value = ""
Me.LastName.Value = ""
Me.ContactCompany.Value = ""
Me.ContactCountry.Value = ""
Me.ContactMail.Value = ""
Me.ContactPhone.Value = ""
Me.ContactCell.Value = ""


Me.ContactPosition.Value = ""
Me.ContactClass.Value = ""

Me.FemButton.Value = ""
Me.MascButton.Value = ""




End Sub



Private Sub UserForm_Activate()

    With Me.ContactClass
    .Clear
    .AddItem ""
    .AddItem "Cliente Final"
    .AddItem "GSA"
    .AddItem "Distribuidor"
    .AddItem "Markem Imaje"
    .AddItem "Competencia"
       
    End With
   
    With Me.ContactPosition
    .Clear
    .AddItem ""
    .AddItem "Gerente"
    .AddItem "Técnico"
    .AddItem "Ventas"
    .AddItem "Comprador"
    .AddItem "Director"
    End With
   
   
End Sub

Thanks in advance
 
Last edited by a moderator:
Upvote 0
Please edit your post and add code tags, I'm not even going to attempt trying to read that much code unformatted.

You're past the edit window now.

What is in the combobox?

Is there a match for it in the column that is being searched?
 
Last edited:
Upvote 0
You are completely right Jason,

let me prepare the code as should be.
 
Upvote 0
jasonb75 said:
Please edit your post and add code tags, I'm not even going to attempt trying to read that much code unformatted.

You're past the edit window now.

What is in the combobox?

Is there a match for it in the column that is being searched?
Click to expand...
Thank you for the edit, Jason,

In the combobox is the contact name of the contact information and it should have a match with information from column B to column J
 
Upvote 0
kalcerro_1 said:
In the combobox is the contact name
Click to expand...
Here lies your problem, a name will be a String, CLng is expecting a number, see if this fixes it.
i = Application.Match(Me.ComboBox2.Value, sh.Range("A:A"), 0)
Personally, I would use range find instead of application match,

VBA Code: 
Dim myRange As Range
With sh
    Set my Range = Intersect(.Range("A:A").Find(Me.ComboBox2.Value, .Range("A1"), xlValues, xlWhole)).EntireRow, .Range("B:J"))

    Me.LastName.Value =myRange(1).Value
    Me.ContactCompany.Value = myRange(2).Value
    Me.ContactCountry.Value = myRange(3).Value
    Me.ContactMail.Value = myRange(4).Value
    Me.ContactPhone.Value = myRange(5).Value
    Me.ContactCell.Value = myRange(6).Value
    Me.ContactPosition.Value = myRange(7).Value
    Me.ContactClass.Value = myRange(8).Value

    If myRange(9).Value = "Masculino" Then Me.MascButton.Value = True
    If myRange(9).Value = "Femenino" Then Me.FemButton.Value = True
End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
3x dependent drop-downs
Replies
0
Views
187
JohnBi
J
A
  • Question
Worksheet from a template using a master list but protecting existing worksheets
Replies
0
Views
227
Alfalfa166
A
S
  • Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
646
NdNoviceHlp
NdNoviceHlp
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
328
pure vito
P
S
  • Question
can you help me in modifay this code for combining data in listbox on userform and calculate values amongst them?
Replies
3
Views
494
saftawy1
S

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top