VBA - Find keyword then count rows until another keyword appears

[AydenM]

Hi,

I am relatively new to VBA and currently learning as I go. I am able to do find and count the cells with a particular keyword but cannot figure out how to do the following:

1. Locate "Task1" on column C then
2. Count the rows below "Task1" until another task appears (Task2,...) then
3. Insert the row count # for "Task1" to cell E1
4. Loop step 1 and 2 for "Task2"
5. Insert the row count # for "Task2" to cell F1

Any help is greatly appreciated. Thank you.

-Regard.

 

[ACommandLineKindaGuy]

This is a bit clunky but it will do what you need. It assumes you have some empty rows in column C both above and below the tasks/entries as shown on your image
Note that instead of putting the row counts in row 1 I put them in row 2 and used row 1 to identify the task associated with the row count

Sub Test()

Dim col, i As Integer
Dim c As Range

col = 5 'This is column E

i = 0
For Each c In Columns(3).Cells
    If c.Value Like "Task*" Then
        Cells(1, col) = c.Value
        If col > 5 Then
            Cells(2, col - 1) = i - 1
        End If
        i = 0
        col = col + 1
    ElseIf IsEmpty(c) And col > 5 Then
        Cells(2, col - 1) = i - 1
        Exit For
    End If
    i = i + 1
Next c

End Sub

 

[rlv01]

Another approach.

Sub CountTasks()
    Dim rng As Range, R As Range
    Dim TaskCount As Long, EntryCount As Long
    Dim CountMode As Boolean
    
    'Set column range
    With ActiveSheet
        Set rng = .Range("C1", .Range("C" & .Rows.Count).End(xlUp))
    End With
    
    'Initialize
    CountMode = False
    EntryCount = 0
    TaskCount = 1
    
    'Count
    For Each R In rng
        If CountMode Then
            EntryCount = EntryCount + 1
        End If
        
        If Left(R.Value, 4) = "Task" Then
            CountMode = True
        End If
        
        If EntryCount > 0 And (Left(R.Value, 4) = "Task" Or Trim(R.Value) = "") Then
            EntryCount = EntryCount - 1
            Range("E1").Offset(0, TaskCount - 1).Value = EntryCount
            EntryCount = 0
            TaskCount = TaskCount + 1
            If Trim(R.Value) = "" Then
                CountMode = False
            End If
        End If
    Next R
    
    'Finalize
    If EntryCount > 0 Then
        Range("E1").Offset(0, TaskCount - 1).Value = EntryCount
    End If
End Sub

 

[ACommandLineKindaGuy]

My code is shorter than yours