Matching Number in Textbox

[Akw47]

Hello Excel Users,

I come with a request. Hope all are doing fine.
So I have 1 textbox to type in numbers etc. 12345 (it will always be 5 digits).
It will have to match with the same number in the same colunm B.

 With .Range("B1", .Cells(.rows.Count, "B").End(xlUp))

After matching, textbox 2 will show me it match 1 time.
Then textbox.1 immediately gets cleared and I typed in the same number 12345 again.
Could you help me make textbox 2 show me 2?

Thanks

 

[Akw47]

i wouldn't use the Change event, but the AfterUpdate

Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag Then
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        TextBox2.Text = "1"
        TextBox2.Tag = TextBox1.Text
    End If
End Sub

wow mike, yours work as well. However, where should I add the count if the number match with the column.

 

[Akw47]

wow mike, yours work as well. However, where should I add the count if the number match with the column.

"count if" e.g WorksheetFunction.CountIf(Range("B:B"), TextBox1) > 0

 

[Akw47]

Every character in Excel can be referred to by its Ascii code
examples
A -Z are 65 -to 90
a - z are 97 to 122
0 - 9 are 48 to 57 (used in the code above)

TextBox1_KeyPress is triggered by each keystroke and keyAscii is the Ascii value of that keystoke which we are able to use
The code above cancels the last keystroke if it is not a number

This formula placed in a cell returns the character number of the FIRST character in A1
=CODE(A1)

I understand now. Thanks for your help yongle

 

[mikerickson]

Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        If IsNumeric(Application.Match(TextBox1.Text, Range("B:B"), 0)) Then
            TextBox2.Text = "1"
            TextBox2.Tag = TextBox1.Text
        Else
            TextBox2.Tag = vbNullString
            TextBox2.Text = "not"
        End If
    End If
End Sub

 

[Akw47]

Private Sub TextBox1_AfterUpdate()
    If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen
        TextBox2.Text = Val(TextBox2.Text) + 1
    Else
        If IsNumeric(Application.Match(TextBox1.Text, Range("B:B"), 0)) Then
            TextBox2.Text = "1"
            TextBox2.Tag = TextBox1.Text
        Else
            TextBox2.Tag = vbNullString
            TextBox2.Text = "not"
        End If
    End If
End Sub

seems to be a error with

If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen

 

[mikerickson]

seems to be a error with

If TextBox1.Text = TextBox2.Tag And TextBox1.Text <> vbNullStringThen

Yes there is. A space between String and Then.

 

[Akw47]

Yes there is. A space between String and Then.

Hi mike, did the changes, and it still can't perform the finding function, of the column B. It keeps getting "not" even when the number matches with what is in one of the cells.

 

[Yongle]

However, I realised that the number of times the data matched is not saved. E.g after typing 12345 twice, it pops out 2 in textbox.2 However, after typing another number like 23456 and back to 12345, textbox.2 starts from 1 again. Is it possible to fix that? thankss

try this

Option Explicit
Private aDict As Object
Private Sub TextBox1_Change()
    Dim T As String
    If Len(TextBox1) < 5 Then Exit Sub
    T = TextBox1
    TextBox1 = ""
    TextBox2 = ""
    If aDict Is Nothing Then Set aDict = CreateObject("Scripting.Dictionary")
    If WorksheetFunction.CountIf(Range("B:B"), T) > 0 Then
        If aDict.Exists(T) Then aDict(T) = aDict(T) + 1 Else aDict.Add T, 1
        TextBox2 = Right(aDict(T), 5)
    End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Len(TextBox1) = 5 Then KeyAscii = 0
    Select Case KeyAscii
        Case 48 To 57
        Case Else: KeyAscii = 0
    End Select
End Sub

 

[Akw47]

try this

Option Explicit
Private aDict As Object
Private Sub TextBox1_Change()
    Dim T As String
    If Len(TextBox1) < 5 Then Exit Sub
    T = TextBox1
    TextBox1 = ""
    TextBox2 = ""
    If aDict Is Nothing Then Set aDict = CreateObject("Scripting.Dictionary")
    If WorksheetFunction.CountIf(Range("B:B"), T) > 0 Then
        If aDict.Exists(T) Then aDict(T) = aDict(T) + 1 Else aDict.Add T, 1
        TextBox2 = Right(aDict(T), 5)
    End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Len(TextBox1) = 5 Then KeyAscii = 0
    Select Case KeyAscii
        Case 48 To 57
        Case Else: KeyAscii = 0
    End Select
End Sub

That it! you got me. Thanks for your help. Could you explan to me about the aDict function that you used?

 

[Yongle]

Could you explan to me about the aDict function that you used?

A VBA dictionary is used to store a "list of items" and associated "values"

In the code above ... aDict stores a "list of 5 digit numbers" and associated "count"
The dictionary is checked when a 5 digit number is entered in textbox1
... numbers not in the dictionary are inserted and given the value 1
... otherwise the value is increased by 1

So aDict looks like this
12345 2
12346 1
12349 5

For fuller explanation of dictionaries:

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com