Macro help - interesting problem

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home version (updated). I have a worksheet that contains over 4000 rows of data. Column J will contain a two-digit number identifying a year. For example, 60 will idicate1960, 70 will indicate 1970. As the worksheet is used, Column J of each Row will be filled in according to the year of the data being used from that Row. The Top Row of the worksheet will be Frozen and I will place a Command Button in Column J. of Row A I would like to create a Macro to be assigned to the Command Button that will, on demand, examine the entire worksheet to Count the number of times that a particular two-digit year has been entered in Column J. The result of the search can be placed in Column K of Row A. I can create most of the Macro, but I have no idea how to make the Macro allow me to enter a specific two-digit number to search for in Column J while the Macro is running. Obviously, the two-digit entry will be different each time the Macro is run. I will appreciate any help with this.
Thank you,
Dan Wilson...

 

[offthelip]

try this, it checks that a number is entered so if you enter characters is asks for a number, it also check that the number is between 0 a 99

Sub input2nums()
Dim tt As Integer
On Error GoTo typeerror
 tt = InputBox(prompt:="Enter a two digit number", Title:="Year Selection")
If tt > 99 Or tt < 0 Then
 MsgBox ("Invalid number")
End If
MsgBox tt                 ' this is just to demonstrate the result. 
Exit Sub
typeerror:
 MsgBox ("Please enter a number!!")
End Sub

 

[Micron]

I have no idea how to make the Macro allow me to enter a specific two-digit number to search for in Column J while the Macro is running.

Input box function? You could type it in or select a range.

 

[Dan Wilson]

Good day offthelip. Thank you for responding to my request. What you gave me allows me to enter the two-digit year, but it does not include searching for the sum of the two-digit years found in in Column J. I have resolved the issue by entering a COUNTIF function in Cell J1 to do a sum using K1 as the value to search for. I will change K1 as necessary. It looks like I'm not as good with Macros as I thought I was. If you want to add the missing search element to the Macro that you sent, I would appreciate knowing how to do that.
Thank you,
Dan Wilson...

 

[Dan Wilson]

Good day Micron. Thank you for responding to my request. I tried using the INPUTBOX function, but I'm not having any luck with it.
Dan Wilson...

 

[offthelip]

The way I would do it is like this:

Sub input2nums()
Dim tt As Integer
On Error GoTo typeerror
 tt = InputBox(prompt:="Enter a two digit number", Title:="Year Selection")
If tt > 99 Or tt < 0 Then
 MsgBox ("Invalid number")
 Exit Sub
End If
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
cnt = Application.WorksheetFunction.CountIf(Range("J1:J" & LastRow), tt)
MsgBox cnt
Exit Sub
typeerror:
 MsgBox ("Please enter a number!!")
End Sub

 

[Dan Wilson]

Good day again offthelip. Thank you for following up. It took me a while to understand what was going on, but I figured it out. The biggest thing I learned was how to include a function (like COUNTIF) into a Macro to make it work. I took out the elements for the LastRow, Dimensioned CNT as an Integer and all is well.
Thank you,
Dan Wilson...

 

[offthelip]

You commented that you dimensioned cnt as an integer. You might think that that I just forgot to dimension it, however I deliberately didn't declare it and I will explain why:
I never use option explicit and only add a dimension statement when it is really necessary. Leaving cnt undeclared means it defaults to being a variant which works perfectly well, and in this code the countif produces an integer while the msgbox I used to display it requires a string, so really it should be a variant. However Vba sorts it out if you declare it as a long or an integer
However this code is one example of where the dim statement I did put in makes a difference to the functionality.
I put the statement :

Dim tt As Integer

I put this in deliberately because this means that if you type in a letter into the inputbox it will generate a type mismatch error and so the the line:

 tt = InputBox(prompt:="Enter a two digit number", Title:="Year Selection")

goes straight to:

typeerror:
 MsgBox ("Please enter a number!!")

Which gives the correct response for the input.
If I had left the dim statement out then tt would default to a variant and so the input box would not throw an error and the code go on to the next line and check whether tt was between 0 and 99 which would fail so to :

If tt > 99 Or tt < 0 Then
 MsgBox ("Invalid number")

so you get " Invalid Number" message which isn't quite correct.
The reason I am writing all of this is make you aware of one of my complaints about using option explicit which forces you to declare every variable, is that most code ends up with lines and lines of declaration most of which tell the user nothing and can often hide the one declaration that really matters.
If I came back to look at this code where I have put one declaration in six months time, the one declaration I put in would stand out to me and immediately trigger a question in my mind: why did need to declare that variable there must someting special about it. ( and there is it changes the functionality)
So I favour only declaring variables when the excel default declaration doesn't work or would change the way it works. This means understanding the default declarations that excel makes.
I think it makes code much easier to read and it certainly saves on typing loads of rows of redundant repetitive code.

 

[Dan Wilson]

Good day again offthelip. Thank you for the explanation. I appreciate the extra help. All I know is that without dimensioning the CNT, all I ended up with was a "0" in the msgbox instead of the count of the two-digit entry. I will keep rereading your entry and playing with it until I understand it better. It's good to have the explanation.
Thank you,
Dan Wilson...