Application.Match help

[PARSONSAA]

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-comfficeffice" /><o></o>
<o></o>
Second let me say that this is my first of many posts.<o></o>
<o></o>
Okay on with it.. I am getting the following error<o></o>
<o></o>
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></o>
I have bolded where debug highlights yellow<o></o>
<o></o>
Any help would be of great services <o></o>
<o></o>
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></o>
Thanks



<o></o>
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

 

[Richard Schollar]

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]

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]

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]

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