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
how would you go about doing this
Write a macro.
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.
Many thanks!
@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.
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
You can change the searchRange
from A1:Z100
for example to A1:Z1000
to include rows up to 999, and so on.
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?
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.
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
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.
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
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.