Application.Match help

PARSONSAA

New Member
Joined
Jul 10, 2008
Messages
22
First let me say that I have used this forum over the last few days and am very pleased with all the information I have found.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Second let me say that this is my first of many posts.<o:p></o:p>
<o:p></o:p>
Okay on with it.. I am getting the following error<o:p></o:p>
<o:p></o:p>
Runtime error 13 type mismatch when I run the macro and select (using the combo box) row 9 that is a number (without any letters). I am trying to be able to use both numbers and text and a combination of both. Have just a number in a row that i select in the macro causes the error.

<o:p></o:p>
I have bolded where debug highlights yellow<o:p></o:p>
<o:p></o:p>
Any help would be of great services :)<o:p></o:p>
<o:p></o:p>
I would attach the file but it seems I do not have permissions yet so here is the code below. If you would like me to email it please let me know.<o:p></o:p>
Thanks



<o:p></o:p>
Public myRow As Long
Private Sub ComboBox1_Click()
myRow = Application.Match(Me.ComboBox1.Value, Sheets("EPR Tracker").Range("A:A"), 0)
Me.TextBox1.Value = Application.Index(Sheets("EPR Tracker").Columns(2), myRow)
End Sub
Private Sub CommandButton1_Click()
' Stores the name for search value
NameOrig = TextBox1.Value

'Finds the name from the dropdown and deletes it
With Worksheets("EPR Tracker").Range("A:A")
.Cells(myRow, 2).Value = TextBox1.Value
' etc.
End With
Unload Me
End Sub
Private Sub UserForm_Initialize()
For Each c In Worksheets("EPR Tracker").Range("A2", Range("A65000").End(xlUp))
Me.ComboBox1.AddItem c.Value
Next c
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi & Welcome to the Board!

I expect that the worksheetfunction isn't finding a match and is thus returning an error value (rather than a Long as would be returned if a match had been found). You could declare myRow as a Variant which should prevent this error. Then you would need to check that the value returned was indeed a numeric value (match found) or an error value (match not found).
 

PARSONSAA

New Member
Joined
Jul 10, 2008
Messages
22
Thank you for your fast reponse.

I changed it from long to Variant and i did not get the error like normal when i select text however i now get a diffrent error on a diffrent line when i select a number the error is below and the line is bolded

Thanks again for your help

Runtime error -2147352571 (80020005)
Could not set the vaule property, type mismatch

Private Sub ComboBox1_Click()
myRow = Application.Match(Me.ComboBox1.Value, Sheets("EPR Tracker").Range("A:A"), 0)
Me.TextBox1.Value = Application.Index(Sheets("EPR Tracker").Columns(2), myRow)
End Sub
Private Sub CommandButton1_Click()
' Stores the name for search value
NameOrig = TextBox1.Value

'Finds the name from the dropdown and deletes it
With Worksheets("EPR Tracker").Range("A:A")
.Cells(myRow, 2).Value = TextBox1.Value
' etc.
End With
Unload Me
End Sub
Private Sub UserForm_Initialize()
For Each c In Worksheets("EPR Tracker").Range("A2", Range("A65000").End(xlUp))
Me.ComboBox1.AddItem c.Value
Next c
End Sub
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Then you would need to check that the value returned was indeed a numeric value (match found) or an error value (match not found).

As I stated first off, for whatever reason, a match is not being found, therefore you do not have a numeric number that you can use in subsequent operations. You need to handle this error ie what do you want the code to do if no match is found?
 

PARSONSAA

New Member
Joined
Jul 10, 2008
Messages
22
If no match is found then i want it to look for the number.... ex

a b c d
1 w 3
2 x1 f
3 rr z
4 1 b
5 54 h



Okay so the macro will:
find 3 when i select w
find f when i select x1
find z when i select rr
will error when i select 1
will error when i select 54

What I would like to be able to do but cant figure out is to be able for a number to be found as well.

Thanks in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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
Top