match function in vba working for columns but not for rows

[Subho]

Hi, this is my first try in vba coding and i am facing error with the following piece of code:

Dim x As Variant
Dim y As Variant
x = Application.Match(Me.cmbEmp_cd1.Value, Sheet3.Range("A2:A500"), False)
y = Application.Match(Me.cmbMatch.Value, Sheet3.Range("B1:XFD1"), False)
While 'y' is getting the right number ,'x' is showing "Error 2042"!!!

Need help urgently!!!

TIA

 

[Jonmo1]

Welcome to the board..

That pretty much means the value wasn't found.

What type of data is in cmbMatch?
What type of values are in Row 1?

Are you sure there's an exact match?
Check for extra spaces

 

[Subho]

Hi,

In Row1 the values are Charatcters while in ColumnA the vales are integers, in fact unique integers.

And I am sure it will be exact match because the values in the ComboBox 'Emp_cd1' are taken from A2:A500 itself.

Thanks.

 

[Jonmo1]

Looks like I missread which one worked and which one didn't..

Thanks for giving the answer to the opposite ones I asked for...

Anyway, comboboxes hold Text Strings.
Even if the source data is numeric.

So the match is looking for a "text number" in a column of real numbers.

Try
x = Application.Match(Val(Me.cmbEmp_cd1.Value), Sheet3.Range("A2:A500"), False)

 

[Jonmo1]

And I am sure it will be exact match because the values in the ComboBox 'Emp_cd1' are taken from A2:A500 itself.

If this is true, then you can use the ListIndex property of the combobox.

x = Me.cmbEmp_cd1.ListIndex + 1

The plus 1 is because the first index# is 0