asked on

Find and count code

Hi,

I have attached a ss where i would like a sub to look for "Dividend" and move one down and count until the blanks, in this example that would be 4. Then paste that figure into the update tab in cell O29.

Then repeat for "Corporate Action"

Thanks
Seamus
test.xls

ASKER CERTIFIED SOLUTION

sdwalker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdwalker

And here's the file
test-sdw.xls

chwong67

Another option

Sub CountText()

Dim Found As Boolean
Dim I As Integer

Sheets("Unit Trust").Select
 Range("A1").Select
 
 Do Until Selection.Row > Sheets("Unit Trust").Cells(Sheets("Unit Trust").Rows.Count, "A").End(xlUp).Row
    
    If Selection.Value <> "" Then
        If UCase(Left(Selection.Value, 8)) = "DIVIDEND" Then
            dvd = True
            cpr = False
            mRow = Selection.Row
        ElseIf UCase(Left(Selection.Value, 16)) = "CORPORATE ACTION" Then
            dvd = False
            cpr = True
            mRow = Selection.Row
        Else
            Found = False
            For I = mRow To Selection.Row - 1
                If ActiveSheet.Cells(I, 1).Value = Selection.Value Then
                    Found = True
                    Exit For
                End If
            Next I
            If Not Found Then
                If dvd Then
                    Cnt_Dvd = Cnt_Dvd + 1
                ElseIf cpr Then
                    Cnt_Cpr = Cnt_Cpr + 1
                End If
            End If
        End If
    End If
    Selection.Offset(1, 0).Select
 Loop

Sheets("Update").Range("O29").Value = Cnt_Dvd
Sheets("Update").Range("O30").Value = Cnt_Cpr

End Sub

Open in new window

Seamus2626

ASKER

Thanks, thats perfect

Chwong, your sub put 3+9 into the sheet where it should be 4+22

Thank you as well

Kind Regards,
Seamus