Indentical

[renrut_5]

In the attached file I am needing textbox1.text to match column A in the spreadsheet. I understand how to get the capital letters but my issue is making the (-), the space, and the lettering identical. I may have one person using the sheet that types it all in together while the next uses a slash as opposed to a hyphen. VBA will not see a recognize any part that is not identical. Please assist.

 

[PeteWright]

Hi @renrut_5
Try this:

Private Sub CommandButton1_Click()
   Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
   Dim CellValue As String, Text As String, i As Integer
 
   regex.pattern = "([a-z]+)[ -/]*([0-9]+)"
   regex.IgnoreCase = True
   regex.Global = True
 
   For i = 1 To 1000
      CellValue = Sheet1.Cells(i, 1).Value
      Text = UCase(regex.Replace(TextBox1.Text, "$1 -$2"))
      If CellValue = Text Then
         TextBox2.Text = Sheet1.Cells(i, 4).Value
         Exit For
      End If
    Next i
End Sub

 

[myall_blues]

The best way is to validate the entry against a pattern:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not UCase(TextBox1.Value) Like "?? -####" Then
        MsgBox "Invalid entry"
        TextBox1.SetFocus
    End If
End Sub

 

[Fuji]

renrut_5​

If REGEX functions are available, in your 365, try

Private Sub CommandButton1_Click()
    Dim s, a, b, i&
    Me.TextBox2 = ""
    If Me.TextBox1 = "" Then Exit Sub
    s = Evaluate("regexreplace(""" & Me.TextBox1 & ""","" *\W *"",char(2))")
    With Sheet1
        With .Range("a1", .Range("a" & Rows.Count).End(xlUp))
            a = .Resize(, 4).Value
            b = .Parent.Evaluate("regexreplace(" & .Address & ","" *\W *"",char(2))")
            For i = 1 To UBound(a, 1)
                If s = b(i, 1) Then Me.TextBox2 = a(i, 4): Exit For
            Next
        End With
    End With
End Sub

 

[renrut_5]

Thank you for the reply. I did mess up when I typed out this quick example. The hyphen is actually between the numbers. Do I just need to move the [ -/]* between the first number and add a second number bracket in? There is a space between the set of letters and the first number similar to
cp 4-5, as opposed to the original cp-45. Sorry for the miscommunication.

 

[Fuji]

If REGEX functions are available

Private Sub CommandButton1_Click()
    Dim s$(3), x
    Me.TextBox2 = ""
    If Me.TextBox1 = "" Then Exit Sub
    s(0) = Chr(34) & Me.TextBox1 & Chr(34)
    s(1) = "regexreplace("
    s(2) = "regexextract("
    s(3) = "let(v," & s(0) & ",a," & s(1) & s(2) & "v,""^\D+""),""[\W_]"",""""),b," & _
           s(1) & s(2) & "v,""\d.+\d[\W_]*$""),""[\W_]"",""""),a&""-""&b)"
    s(0) = Evaluate(s(3))
    Me.TextBox1 = s(0)
    With Sheet1
        x = Application.Match(s(0), .Columns(1), 0)
        If IsNumeric(x) Then Me.TextBox2 = .Cells(x, 4)
    End With
End Sub

Else

Private Sub CommandButton1_Click()
    Dim s$, x
    Me.TextBox2 = ""
    If Me.TextBox1 = "" Then Exit Sub
    s = Me.TextBox1
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^A-Z0-9]"
        s = .Replace(s, "")
        .Pattern = "([A-Z])(\d)"
        s = .Replace(s, "$1-$2")
    End With
    Me.TextBox1 = s
    x = Application.Match(s, Sheet1.Columns(1), 0)
    If IsNumeric(x) Then Me.TextBox2 = Sheet1.Cells(x, 4)
End Sub

 

[Fuji]

Correction(simplified) for use of REGEX function

Private Sub CommandButton1_Click()
    Dim s, x
    Me.TextBox2 = ""
    If Me.TextBox1 = "" Then Exit Sub
    s = Me.TextBox1
    s = Evaluate("regexreplace(regexreplace(""" & s & """,""[\W_]"",""""),""(\D)(\d)"",""$1-$2"")")
    Me.TextBox1 = s
    x = Application.Match(s, Sheet1.Columns(1), 0)
    If IsNumeric(x) Then Me.TextBox2 = Sheet1.Cells(x, 4)
End Sub

 

[renrut_5]

Thank you everyone for the solutions. I have taken a bit from each and have it working. I do appreciate the assistance.