if replacing text, how do you add an extra row underneath?

edit

Hi @michaely,

as Opaque said, you'll most likely need a macro for this.

Here is something that already mostly does what you want. But you might have to patch something to match your exact requirements.

Sub SearchAndDoStuff

    Const needle = "mike007"
    Const searchRange = "A1:Z100"
    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oRange = oSheet.getCellRangeByName(searchRange)

        For i = oRange.Rows.getCount() - 1  To 0 Step -1   rem bottom up 
            For j = 0 To oRange.Columns.getCount() - 1
                Set oCell = oRange.getCellByPosition( j, i )
                idx = InStr(oCell.String,needle)
                If (  idx > 0) Then
                        oCell.String = needle
                        oSheet.Rows.insertByIndex(i+1,j)  REM Add Row below 
                        oCell = oRange.getCellByPosition( j, i+1 )
                        oCell.String = "moppy"
                Endif
            Next 
        Next 
End Sub

Result:

If one need further help, just ask in the comments. Hope it helps.

Update: 2021-01-21

To add a row above the found term, one can use this:

Sub SearchAndDoStuff2

    Const needle = "mike007"
    Const searchRange = "A1:C6"
    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oRange = oSheet.getCellRangeByName(searchRange)

    jump = False
    For i = 0 To oRange.Rows.getCount() - 1 
        For j = 0 To oRange.Columns.getCount() - 1
            Set oCell = oRange.getCellByPosition( j, i )
            idx = InStr(oCell.String,needle)
            If (  idx > 0) Then
                    oCell.String = needle
                    oSheet.Rows.insertByIndex(i,1)
                    oRange = oSheet.getCellRangeByName(searchRange)
                    oCell = oRange.getCellByPosition( j, i )
                    oCell.String = "moppy"
                    jump=True
            Endif
        Next
        if jump then
            i=i+1
        endif
        jump=False
    Next 
End Sub

Result:

If one need further help, just ask in the comments. Hope it helps.