Message Box showing number of times a certain word followed by a digit occurs first in rows in column

[Ironman]

Hi

Sheet 'Training 1981-1997' Column F2:F6210 contains text, with some rows that start with the word "Day" (without the commas) followed by a space and a number.

I need to cross-check to make sure I haven't skipped or duplicated a number in error. Therefore what I'd be grateful for is some double click event code I can run in any cell in Col G that will return a msgbox that has counted the number of cells in Col F2:F6210 with text beginning with the word "Day" followed by a space and a digit e.g. "Day 123". I've used the 'Find' function to do this so far, but it'll be faster just to double click.

Many thanks!

 

[My Aswer Is This]

Hi

Sheet 'Training 1981-1997' Column F2:F6210 contains text, with some rows that start with the word "Day" (without the commas) followed by a space and a number.

I need to cross-check to make sure I haven't skipped or duplicated a number in error. Therefore what I'd be grateful for is some double click event code I can run in any cell in Col G that will return a msgbox that has counted the number of cells in Col F2:F6210 with text beginning with the word "Day" followed by a space and a digit e.g. "Day 123". I've used the 'Find' function to do this so far, but it'll be faster just to double click.

Many thanks!

So you want to double click on any cell in range "F2:F6210" and have a message box popup and tell you how many cells in this range that has "Day 123".

So why not double click on just one cell to get the answer?

 

[Ironman]

Hi again!

Yes, that's what I will be doing. To explain, I'm manually transferring data from a paper diary to Excel, so this is a work in progress. In essence, I'll be clicking the cell in Col G adjacent to the latest entry in Col F that I'm working on. I stated "any cell" for simplicity's sake, else I could say "Col G of the last row where Col B contains data".

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
When you double click on any cell in column G The script will run:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  2/6/2022  8:28:41 AM  EST
If Target.Column = 7 Then
Cancel = True
Dim r As Range
Dim e As Long
e = 0
For Each r In Range("F2:F6210")
    If r.Value = "Day 123" Then e = e + 1
Next
MsgBox e
End If
End Sub

 

[Ironman]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
When you double click on any cell in column G The script will run:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  2/6/2022  8:28:41 AM  EST
If Target.Column = 7 Then
Cancel = True
Dim r As Range
Dim e As Long
e = 0
For Each r In Range("F2:F6210")
    If r.Value = "Day 123" Then e = e + 1
Next
MsgBox e
End If
End Sub

Hi, many thanks - I'm just out at the moment but I've noted your code includes "Day 123". I didn't mean that literally, that was only an example. It's all occurrences of the word Day followed by a space and then any digit.

Thanks again!

 

[My Aswer Is This]

Hi, many thanks - I'm just out at the moment but I've noted your code includes "Day 123". I didn't mean that literally, that was only an example. It's all occurrences of the word Day followed by a space and then any digit.

Thanks again!

I'm not sure how to search for "Day " & any other value
so it might be "Day Apple" Or Day Pear"

 

[Ironman]

I'm not sure how to search for "Day " & any other value
so it might be "Day Apple" Or Day Pear"

Hmm I do really need it to be a space followed by a digit or there will be some unwanted data included in the result.

 

[My Aswer Is This]

Hmm I do really need it to be a space followed by a digit or there will be some unwanted data included in the result.

Hmm I do really need it to be a space followed by a digit or there will be some unwanted data included in the result.

And when you say "Digit" does that mean a character like A or B or do you mean a number like 12 or 13

 

[Ironman]

And when you say "Digit" does that mean a character like A or B or do you mean a number like 12 or 13

Any number

 

[My Aswer Is This]

Sorry I guess I did not notice the part about Day and as a example