Mental block on userform code basics

[jasonb75]

Hi all,

Can somebody please put me right before I go insane, I know this should be simple but I've only used match functions in VBA a couple of times and I can't get the syntax to work right.

I need to look for an exact match for the textbox entry, first in row 3, then in row 1, with a message if no match found.

On Error GoTo line2
a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A3:CU3"), 0)
GoTo Line4
line2:
On Error GoTo line3
a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A1:CU1"), 0)
GoTo Line4
line3:
MsgBox Edit1.TextBox1.Value & " Not Found, Please chack and try again.", vbInformation & vbOKOnly

Line4:

Thanks in advance.

 

[Legacy 68668]

You can not generate RunTime Error with Application.Match method.

a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A3:CU3"), 0)
If IsError(a) Then GoTo line2
GoTo Line4
line2:
a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A1:CU1"), 0)
If IsError(a) Then GoTo line3
GoTo Line4
line3:
MsgBox Edit1.TextBox1.Value & " Not Found, Please chack and try again.", vbInformation & vbOKOnly

Line4:

or

On Error GoTo line2
a = Application.WorksheeteFunction.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A3:CU3"), 0)
GoTo Line4
line2:
On Error GoTo line3
a = Application.WorksheeteFunction.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A1:CU1"), 0)
GoTo Line4
line3:
MsgBox Edit1.TextBox1.Value & " Not Found, Please chack and try again.", vbInformation & vbOKOnly

Line4:

 

[jasonb75]

Thanks Seiya, I couldn't get this right but with your help it's making sense now

 

[jasonb75]

Ok, maybe not as simple as it first looked,

a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A3:CU3"), 0)
If IsError(a) Then GoTo line2
GoTo Line4
line2:
a = Application.Match(Edit1.TextBox1.Value, Sheets("Data_Sheet").Range("A1:CU1"), 0)
If IsError(a) Then GoTo line3
GoTo Line4
line3:
MsgBox Edit1.TextBox1.Value & " Not Found, Please chack and try again.", vbInformation & vbOKOnly

Line4:

Always results in the messagebox, for valid and invalid entries, tried changing .Value to .Text in case it was being misread by the code but still the same.

The other block of code gives RunTime error 438, highlighting the second function.match line of the code.

I've tried a few things with both but so far no success.

Any other ideas?

 

[Legacy 68668]

1) How did you declared the data type of variable a ?
2) What is the data type of Edit1.TextBox1.Value and Sheets("Data_Sheet").Range("A3:CU3") ?

If you are trying to match String with the Numeric, it always returns "N/A" which also returns true out of IsError function.

 

[jasonb75]

Variable is declared as Long
Sheet is formatted as general and all values are numeric (tested with =SUM(A1+A3), etc along entire row).

Just realised that I had changed the variable from a to f but not changed the declaration.

Now that's been corrected, first code gives type mismatch error 13 on first line.

second code still gives error 438 on same line as before.

Jason

edit: just realised that variable should be declared as integer, this should be holding the position of the match in the array.
the lookup value in the textbox is 10 digits, could that be the cause of the problems?

 

[Legacy 68668]

1) a MUST be varinat, since it should also accept "N/A" error
2) change Edit1.TextBox1.Value to Val(Edit1.TextBox1.Value) in order to convert string value to a Numeric value

 

[jasonb75]

Thanks Seiya, that solved it