Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 2
1 Solution
 
sdwalkerCommented:
This is not beautiful, but it's understandable and it works.
Sub FindMyText()

endRow = Sheets("Unit Trust").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To endRow
  If UCase(Left(Sheets("Unit Trust").Range("A" & i).Value, 8)) = "DIVIDEND" Then
    firstDividend = i
  End If

  If lastDividend = "" Then
    If Sheets("Unit Trust").Range("A" & i).Value = "" And firstDividend <> "" Then
      lastDividend = i - 1
    End If
  End If
  
  If UCase(Left(Sheets("Unit Trust").Range("A" & i).Value, 16)) = "CORPORATE ACTION" Then
    rowCorporate = i
    Exit For
  End If

Next i

Sheets("Update").Range("O29").Value = lastDividend - firstDividend
Sheets("Update").Range("O30").Value = endRow - rowCorporate

End Sub

Open in new window

0
 
sdwalkerCommented:
And here's the file
test-sdw.xls
0
 
chwong67Commented:
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

0
 
Seamus2626Author Commented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now