Ask Your Question
1

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

asked 2021-01-02 10:31:36 +0200

michaely gravatar image

updated 2021-01-02 13:16:34 +0200

Eg I wanted to replace all cells that contain “mike007” with “mike007” and then adding an extra row under that cell that has “moppy” writtenin it, how would you go about doing this? https://i.imgur.com/Gva61j7.png so i want to change it to like this https://i.imgur.com/wlWrcqB.png

edit retag flag offensive close merge delete

Comments

1

how would you go about doing this

Write a macro.

Opaque gravatar imageOpaque ( 2021-01-02 12:38:32 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2021-01-02 21:35:07 +0200

igorlius gravatar image

updated 2021-01-21 15:59:43 +0200

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:

image description

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: image description

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

edit flag offensive delete link more

Comments

1

Many thanks!

michaely gravatar imagemichaely ( 2021-01-02 22:56:09 +0200 )edit

@michaely If an answer solved your problem or answerd your question, please mark it as correct. Helps to keep the site usable for everybody. Thanks.

igorlius gravatar imageigorlius ( 2021-01-03 23:06:00 +0200 )edit

The macro runs almost perfectly, one small problem though - for some reason the macro only runs on cells up to row 99, anything past row 100 it's not inserting any new content

michaely gravatar imagemichaely ( 2021-01-14 15:16:03 +0200 )edit
1

You can change the searchRange from A1:Z100 for example to A1:Z1000 to include rows up to 999, and so on.

igorlius gravatar imageigorlius ( 2021-01-14 17:15:00 +0200 )edit

one quick last question buddy, if I wanted to add an extra row ABOVE (Rather than underneath) can you tell me what change I'd make to the script?

michaely gravatar imagemichaely ( 2021-01-21 14:32:35 +0200 )edit
1

buddy ... is an unusual way to address someone, but ... i dont mind. I updated my answer, but my solution is a little more complex than i personally think it should be, but ... it works. Improvements welcome.

igorlius gravatar imageigorlius ( 2021-01-21 15:57:46 +0200 )edit

I've another question if you don't mind - can you tell me what code you'd use to insert text into the row LEFT of rows that contain certain lines of text? Eg see here this row has “lemons68” in it, id like to add text to the row left of all rows that contain “lemons68” https://i.imgur.com/Ebk7UUd.png

michaely gravatar imagemichaely ( 2021-03-16 13:36:52 +0200 )edit

Something like this, maybe?

Sub SearchAndDoStuff3
    Const needle = "lemons68"
    Const searchRange = "B1:C6"
    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oRange = oSheet.getCellRangeByName(searchRange)

    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 = oRange.getCellByPosition( j-1, i )
                    oCell.String = "lemonjuice68"
            Endif
        Next
    Next 
End Sub

Please be aware that there is no range check, so if your search term is in Column A ... you'll get an exception.

Hope it helps.

igorlius gravatar imageigorlius ( 2021-03-16 14:04:48 +0200 )edit

Thanks, can you tell me what changes I'd need to make so it works in column A? I'm trying to get it to enter the text into A23 [which is left of jack] https://i.imgur.com/FcDSbqm.png

michaely gravatar imagemichaely ( 2021-03-17 12:34:01 +0200 )edit

You just have to change the searchRange from

"B1:C6" to (for example) "B1:B23"

So the entire area you want to look for lemons68 needs to be included.

igorlius gravatar imageigorlius ( 2021-03-17 17:53:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-02 10:31:36 +0200

Seen: 87 times

Last updated: Jan 21