Counting the number of times scanned in excel

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello,
Is it possible to have a code that when i type a number in the text box and it matches with one of the cell in the entire coloumn of B. It will make my other textbox display 1 time. And if it comes across the same number again, the display will show 2 times.

I am using ActiveX control on a userform. Thanks very much :)
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
How about ...

VBA Code:
Private Sub TextBox1_Change()

    Dim c As Range, sAddr As String, i As Long

    With ThisWorkbook.ActiveSheet
        With .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
            Set c = .Find(What:=CStr(TextBox1.Value), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
            If Not c Is Nothing Then
                sAddr = c.Address
                Do
                    i = i + 1
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And sAddr <> c.Address
            End If
            TextBox2.Value = i
        End With
    End With
End Sub
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello, it seems as though when i type in the number in the text box, it can register that it was recorded once. However, after typing it again, the display could not register it as the 2 time
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello, it seems as though when i type in the number in the text box, it can register that it was recorded once. However, after typing it again, the display could not register it as the 2 time
and I understand your code, if it found 2 of the same number in the coloum, it will display 2. But I am actually looking more towards, typing the same number twice, for it to display 2.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I see, must have misunderstood.

But I am actually looking more towards, typing the same number twice, for it to display 2.
Are you talking about numbers (like 831 or 29) or characters? A three-digit number originates from three keystrokes, so how do you want to determine if 831831 is just one single number or 831 twice?
I have to admit, I don't yet understand what you want in the end.
 

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I see, must have misunderstood.


Are you talking about numbers (like 831 or 29) or characters? A three-digit number originates from three keystrokes, so how do you want to determine if 831831 is just one single number or 831 twice?
I have to admit, I don't yet understand what you want in the end.
i confused you i am so sorry.
I am taling about 5 digits numbers. So if i type 12345 and there is a 12345 in column B, the other textbox2. will note as 1. If i type in textbox 1 the same 12345 again it should give me 2 for textbox 2.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
I am taling about 5 digits numbers. So if i type 12345 and there is a 12345 in column B, the other textbox2. will note as 1. If i type in textbox 1 the same 12345 again it should give me 2 for textbox 2.
So you are talking about a kind of logging system.
That requires its own approach. The correct approach is determined by factors such as the following:
- must the user's input be confirmed?
- is it always about five digits or not?
- is it always the same column or not?
- can the logging be volatile or not?
We are not talking about a few lines of code, but about a complete system. So for now I will drop out.
 
Solution

Akw47

Board Regular
Joined
Nov 6, 2020
Messages
90
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
So you are talking about a kind of logging system.
That requires its own approach. The correct approach is determined by factors such as the following:
- must the user's input be confirmed?
- is it always about five digits or not?
- is it always the same column or not?
- can the logging be volatile or not?
We are not talking about a few lines of code, but about a complete system. So for now I will drop out.
Yea i guess it is kind of a logging system. Thanks for your help though :)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,569
Members
410,560
Latest member
1ndependent
Top