Error 13 - Mismatch in UserForm update Application.Match with mixed Data

[Poliv]

Hello Everyone,

I am a newbie here and would like to thank you all for your valuable input that I read in this forum.
Unfortunately, I couldn't find my answer within the forum, therefore I would like to ask this to all of you.

I have a User Form to update my database.
This "Update Entry" Userform calls the information related to an Order number.
This Order number as always been a number.
But now, our Order numbers integrate letters.

The problem is that my formula doesn't work anymore and I do not know why ...

This is what I wrote (where ComboBox4 is the dropdown list of the Order numbers stored in my DataBase) :


Private Sub ComboBox4_Change()

If Me.ComboBox4.Value <> "" Then

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DataMaster")

Dim n As Integer
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)

Me.TextBox1.Value = sh.Range("B" & n).Value
Me.TextBox2.Value = sh.Range("C" & n).Value
Me.DTPicker1.Value = sh.Range("D" & n).Value
If sh.Range("E" & n).Value = "eMail" Then Me.OptionButton1 = True
If sh.Range("E" & n).Value = "Phone" Then Me.OptionButton4 = True
If sh.Range("E" & n).Value = "Customer Thermometer" Then Me.OptionButton3 = True
Me.TextBox3.Value = sh.Range("F" & n).Value
Me.TextBox4.Value = sh.Range("G" & n).Value
Me.ComboBox1.Value = sh.Range("H" & n).Value
Me.ComboBox2.Value = sh.Range("I" & n).Value
Me.ComboBox3.Value = sh.Range("J" & n).Value
Me.TextBox5.Value = sh.Range("K" & n).Value

End If


End Sub


It is working like a charm for all "Numerical Only" Order numbers, but it gives me an :
"Error 13 : Mismatch" for all Order numbers containing a Letter. With an Highlight on the following part :
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)

I tried to do the followings :
Change the variable from Integer to Long => No result
To declare the VBA.Clng to Val => No result

The result is that I am stuck ...
If anyone got an idea thank you for sharing.
And thank you in advance for taking the time to read this ... for those who do :biggrin:

Cheers,

[SamT]

CLng will throw an error if the input, (ComboBox4) contains a non numerical character.

Try

n = sh.Range("A:A").Find(Me.ComboBox4).Row


BTW, the Hashmark symbol, (#,) will insert VBA Code brackets around your code

Hint for newbies: your code will be more self documented if you name your Form Controls similar to the Data Sheet's Headers.
Example: Column A header = Order_number. Control ComboBox4.Name = cmboOrder_Number... You can use a UserDefinedType where colOrder_Number = 1... and the variable n is named RecordRow


Private Type DataMaster_Columns
colOrder_Number = 1
colNextHeader1 'Generic
colNextHeader2 'Generic
colOrderDate
colEtc
End Type

Revised code example:

RecordRow = .Columns(colOrder_Number).Find(Me.CmboOrder_Number).Row
With sh. Rows(RecordRow)
Me.txbxNextHeader1 = .Cells(colNextHeader1) 'Generic
Me.txbxNextHeader2 = .Cells(colNextHeader2) 'Generic
Me.dpkrOrder_Date = .Cells(colOrderDate)
Me.obutEmail = .Cells(colCustomerContactType) = "eMail"
Me.obutPhone = .Cells(colCustomerContactType) = ""Phone""
Me.obutCustomer_Thermometer = .Cells(colCustomerContactType) = "Customer Thermometer"
etc
End With

Note this means a lot of CopyPasta (no Typos) and three or four character editing. I usually start by copying the Headers, then PasteSpecial.Transpose into a column on a new sheet. From there I C&P to name all the Form Controls. Then I will place "col" into the preceding Sheet Column and use "=A&B" in the next column and fill down both. Copying that column values gives me the variables for the UserDefined Type.

The act of copying a header name from that vertical list, then switching back to my UserForm means that I don't forget a required Control, as well as preventing typos.

This system is mostly so I never have to think (have a brain fart) about which Range which Control uses.

[Poliv]

Dear Sam,

Thank you very much for these explanations.
You are right it is more self-documented if I name theForm Controls similar to the Data Sheet's Headers - > Good Tip !
I tried the :

n = sh.Range("A:A").Find(Me.ComboBox4).Row

And it's working like a charm. Thank you So much! I have been searching for days and now I will modify my code with the tip you gave me.
Thank you again very much and stay safe during these crazy times :thumb.
Cheers,

P.