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]

OK:
Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  2/6/2022  10:39:22 AM  EST
If Target.Column = 7 Then
Application.ScreenUpdating = False
Cancel = True
Dim r As Range
Dim e As Long
e = 0
For Each r In Range("F2:F6210")
    If r.Value Like "*Day *" Then e = e + 1
Next
End If
MsgBox "We found " & e & " occurances"

Application.ScreenUpdating = True
End Sub

 

[mumps]

Try this macro in the worksheet code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, i As Long, x As Long
    lRow = Range("F" & Rows.Count).End(xlUp).Row
    If Intersect(Target, Range("G" & lRow)) Is Nothing Then Exit Sub
    v = Range("F2:F" & lRow).Value
    For i = LBound(v) To UBound(v)
        If v(i, 1) Like "Day *" And IsNumeric(Mid(v(i, 1), 5)) Then
            x = x + 1
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox x
End Sub

 

[Ironman]

Thanks MAIT - I've double clicked a few cells in Col G and it won't run.

 

[mumps]

Did you try the macro I suggested in Post #12?

 

[Ironman]

Sorry Mumps, I posted within seconds of seeing your post - many thanks, I'm trying it now.

Edit: Same thing as with MAIT's solution - I'm double clicking cells in Col G and nothing's happening. Is there a specific row I need to be trying this with or should it work with all the rows?

 

[My Aswer Is This]

Thanks MAIT - I've double clicked a few cells in Col G and it won't run.

Did you install the script the way my posting mentioned?
You need to double click on column G

 

[Ironman]

Did you install the script the way my posting mentioned?
You need to double click on column G

Yes, exactly as you said. I'm sorry that it isn't working.

 

[My Aswer Is This]

Yes, exactly as you said. I'm sorry that it isn't working.

The script looks in column F for a value like "Day " and any other value
Do you have a value in column F or a formula

And when you say Day I assume you mean the word "Day " and not Todays Date

Show us a example of exactly what you have in column F
And the script looks in the range you said:
Like:
For Each r In Range("F2:F6210")

 

[NoSparks]

Doesn't your existing Worksheet_BeforeDoubleClick have code to exit the sub under certain conditions ?

 

[Ironman]

The script looks in column F for a value like "Day " and any other value
Do you have a value in column F or a formula

And when you say Day I assume you mean the word "Day " and not Todays Date

Show us a example of exactly what you have in column F
And the script looks in the range you said:
Like:
For Each r In Range("F2:F6210")

Col F contains only text.

Yes, Day.

Sure:

Slight pain still from L. shin. WEEK TOTAL = 11 MILES
Day 327. Evening run. Steady pace. Legs still not v. lively for some reason. Left shin not a problem although discomfort there.
Day 328. Evening run. Fast, hard run. Legs better than above but don't feel as light as normal. Not much power in lungs/legs on hills.