Match TextBox Value to values in Range

[GuyGadois]

How would I match a textbox value to any value in a range.

When a user enters in a value in Textbox1 the VBA compares it to a list of numbers to see if it matches. If it does then the sub continues, if not the sub ends. I am able to match it to one cell but not able to get it to work in a a range.

Thanks,

Guy

 

[mikerickson]

What shape range?
This would work for a column or row range

Application.Match(TextBox1.Text, Range("A1:A10"),0)

for a rectangular range, I'd use something like

Range("A1:D10").Find(What:=TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

 

[GuyGadois]

The shape of the range is one row by 10 columns. Rectangular I guess?

Will that work is a named range instead of specifying cells?

Guy

 

[mikerickson]

What happened when you tried it?

 

[GuyGadois]

What happened when you tried it?

Error with naming cells

If Worksheets("Admin").Range("f114:f124").Find(What:=TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) Then

Run-time error '13':

Type Mismatch


Error with name range:

If Worksheets("Admin").Range("Admin_password_range").Find(What:=TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) Then

Run-time error '91':
object variable or with block variable not set

 

[mikerickson]

Worksheet references aren't needed if the Name is scoped to the Workbook level.
This worked for me:

MsgBox Application.Match("Test String", Range("myName"), 0)

The mismatch in the second bit of code is that the function .Find returns a Range object and If needs a Boolean condition.

 

[GuyGadois]

Worksheet references aren't needed if the Name is scoped to the Workbook level.
This worked for me:

MsgBox Application.Match("Test String", Range("myName"), 0)

The mismatch in the second bit of code is that the function .Find returns a Range object and If needs a Boolean condition.

I am afraid that is is probably over my head. I tried replacing the .find to a boolean but that did not work. Then I tried the code:

MsgBox Application.Match("Test String", Range("myName"), 0)</pre>

But couldn't figure out how to use it. I searched a few places on the internet but couldn't find a reasonable explanation what is expected when using a Match function. I have a simple code to work to match one cell:

If TextBox1.Text = Range("name") Then

but, when the range "Name" is a range of multiple cells (one column x 10 rows), it doesn't work.

Guy

 

[mikerickson]

If IsNumeric (Application.Match(TextBox1.Text, Range("name"),0)) Then
    MsgBox "Its in the Range"
Else
    MsgBox "its not"
End If

Dim aRange as Range

On Error Resume Next
    Set aRange = Range("name").Find(what:=TextBox1.Text)
On Error GoTo 0

If aRange Is Nothing Then
    MsgBox "not there"
Else
    MsgBox "Its there"
End If

 

[GuyGadois]

If IsNumeric (Application.Match(TextBox1.Text, Range("name"),0)) Then
    MsgBox "Its in the Range"
Else
    MsgBox "its not"
End If

Dim aRange as Range

On Error Resume Next
    Set aRange = Range("name").Find(what:=TextBox1.Text)
On Error GoTo 0

If aRange Is Nothing Then
    MsgBox "not there"
Else
    MsgBox "Its there"
End If

OK, the second makes more sense to me. Thanks I will firgure out how to apply that one. Thank you very much!

Guy