Enter "Note" below specific text

[Panoos64]

Hi all,
I would like to create a VBA code in excel 2016, so that to run through col. "AK" and where find the text "NET PROFIT / LOSS" should enter below after a blank row the below note:

"Note: The above Net Profit & Loss arises, thereafter we entered the accounts dividends received, banks interest and dividends payable"

Many thanks in advance

 

[Joe4]

Will it only occur once on the sheet, or could it occur more than once?

So, if it found on say, row 41, are you saying that it should insert a blank row under row 41, and place that note in row 42?

 

[Joe4]

If my assumptions are correct, try this. This will work for one or more found entries in the cell:

Sub MyInsertMacro2()

    Dim fRow As Long
    Dim pRow As Long

    pRow = 1
    fRow = 1
    
    Do
    
'       Find value
        Columns("AK:AK").Find(What:="NET PROFIT / LOSS", After:=Cells(pRow, "AK"), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
'       Capture row of found value
        fRow = ActiveCell.Row


'       Exit if found row less than previous row
        If fRow < pRow Then Exit Do
    
'       Insert blank row
        Rows(fRow + 1).Insert
    
'       Enter note in next row
        Range("AK" & fRow + 1) = "Note: The above Net Profit & Loss arises, thereafter we entered the accounts dividends received, banks interest and dividends payable"
    
'       Underline word "Note"
        With Range("AK" & fRow + 1).Characters(Start:=1, Length:=5).Font
            .Underline = xlUnderlineStyleSingle
        End With
        
'       Set previous row value
        pRow = fRow
        
    Loop
    
End Sub

If you do not need to insert a blank row, then just remove that part from the code above.

 

[Panoos64]

Hi Joe and many thanks for your support!
the code works, just a small amend is require. It fall in loop and it still write the note below again and again. I do not know in which row it stop, but i think it write it till end of excel rows. I was ending by end of task because it reach on phase by which it dosen't respond.
Thank you once again and have a great day

 

[Joe4]

just a small amend is require. It fall in loop and it still write the note below again and again.

Not sure why that would be. In your example, what rows does the phrase "NET PROFIT / LOSS" appear in?

Here is some amended code that will not loop more than 100 times, and handles the error of not finding the value at all:

Sub MyInsertMacro2()

    Dim fRow As Long
    Dim pRow As Long
    Dim i As Long

    pRow = 1
    fRow = 1
    
    Do Until i = 100
    
        On Error GoTo err_check
'       Find value
        Columns("AK:AK").Find(What:="NET PROFIT / LOSS", After:=Cells(pRow, "AK"), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
'       Capture row of found value
        fRow = ActiveCell.Row


'       Exit if found row less than previous row
        If fRow < pRow Then Exit Do
    
'       Insert blank row
        Rows(fRow + 1).Insert
    
'       Enter note in next row
        Range("AK" & fRow + 1) = "Note: The above Net Profit & Loss arises, thereafter we entered the accounts dividends received, banks interest and dividends payable"
    
'       Underline word "Note"
        With Range("AK" & fRow + 1).Characters(Start:=1, Length:=5).Font
            .Underline = xlUnderlineStyleSingle
        End With
        
'       Set previous row value
        pRow = fRow
        
'       Loop counter
        i = i + 1
        
    Loop
    On Error GoTo 0
    
    Exit Sub
    
'error handling
err_check:
    If Err.Number = 91 Then
        MsgBox "Cannot find desired string", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[Panoos64]

Hi Joe, it works perfect now and i want to say a great thanks to you for all support you done for my project.

Hv a great lovely day!

 

[Joe4]

You are welcome.