Mental block on userform code basics

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
L

Legacy 68668

Guest
You can not generate RunTime Error with Application.Match method.
Rich (BB code):
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
Rich (BB code):
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Thanks Seiya, I couldn't get this right but with your help it's making sense now :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Ok, maybe not as simple as it first looked,

Code:
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? :)
 
L

Legacy 68668

Guest

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:
L

Legacy 68668

Guest
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Thanks Seiya, that solved it :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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