application.match errors

[wwbwb]

Greetings all,

I have a question about the following code erroring out.

dim res9 as variant
dim res4 as variant

res9 = application.match(combobox1.value, Range("B:B"), 0)
res4 = application.match(combobox2.value, Range("B:B"), 0)

Range("e" & res9).value = userform4.textbox1.value
Range("e" & res4).value = userform4.textbox1.value

Col B is a list of serial numbers. Either 7 digits long (1234567) or 8 alphanumeric (W1234567). The combo boxes are loaded with col B when the userform is initialized. When I run the code, any serial number that begins with a letter, works. Any serial number I select that is strictly a number, errors out (Error 2042). The serial number is indeed there and if I put a letter in front of a 'number only' serial, it works perfectly. Any reason why it can't find the 'number only' serials?

 

[RileyC]

I believe you're likely trying to compare an integer value and a string value, which are completely different. To convert every value to a string you can simply use something similar to below:

Dim x as Int
Dim xStr as String

x = 1234
xStr = CStr(x)

On Error Resume Next
If x = xStr Then
    'Do Stuff
End If
If Err.Number <> 0
    MsgBox("Error, you tried to compare a string and an integer!")
End If

 

[Osvaldo Palmeiro]

You should make some kind of check first for the ComboBox1.value, like below:

 If IsNumeric(ComboBox1.Value) Then
  res9 = Application.Match([COLOR=#ff0000][B]Val[/B][/COLOR](ComboBox1.Value), Range("B:B"), 0)
 Else: res9 = Application.Match(ComboBox1.Value, Range("B:B"), 0)
 End If

 

[wwbwb]

You should make some kind of check first for the ComboBox1.value, like below:

 If IsNumeric(ComboBox1.Value) Then
  res9 = Application.Match([COLOR=#ff0000][B]Val[/B][/COLOR](ComboBox1.Value), Range("B:B"), 0)
 Else: res9 = Application.Match(ComboBox1.Value, Range("B:B"), 0)
 End If

Thank you. This seems to work on a test sample I have. I'll try it more once I get back to work. I'll check to see if the numbers are actually setup as text.