Libre Office Macro to find and move Cell Contents

I'm looking for a quick solution to create a macro in a LO Spreadsheet that will search a range of cells for a text string, then when finding one, move it into a second range of cells.

Here's what I have so far:
Sub ExtractByString()

Dim FindAddress As String
Dim extString As String, WCString
Dim oSheet
Dim oInCells, oOutCells
Dim Found

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oInCells = oSheet.getCellRangeByName("A1:A50000")

extString = InputBox("Please Enter String.", "String Entry!")

WCString = oInCells.createSearchDescriptor
WCString.SearchString= "*" & extString & "*"
WCString.SearchSimilarity = True
WCString.SearchSimilarityAdd = 2
WCString.SearchSimilarityExchange = 2
WCString.SearchSimilarityRemove = 2
WCString.SearchSimilarityRelax = False


If "" = extString Then
    Exit Sub
Else
   Found = oInCells.FindFirst(WCString)

Endif 


End Sub

Open in new window


Now what I'm stuck on is I can't find any documentation on the properties/methods available to do what I want, particularly where the cell range that was found is returned so that I can move it to a different location.

Any thoughts appreciated....
Stan_GAsked:
Who is Participating?
 
Stan_GAuthor Commented:
Got it figured, heres what I wound up doing....


REM  *****  BASIC  *****

Sub ExtractByString()

Dim Root As String, WCRoot
Dim oSheet
Dim oInCells, oOutCells, oCell
Dim Found

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oInCells = oSheet.getCellRangeByName("A1:A50000")
oOutCells = oSheet.getCellRangeByName("I1:I50000")

oOutCells.clearcontents(23)



oRow = 0

oCell = oSheet.getCellByPosition(8, oRow)



Root = InputBox("Please Enter String.", "String Entry!")

WCRoot = oInCells.createSearchDescriptor
WCRoot.SearchString= Root
WCRoot.SearchSimilarity = False

If "" = Root Then
    Exit Sub
Else
   Found = oInCells.FindFirst(WCRoot)
   do while not isnull(Found)
   oCell = oSheet.getCellByPosition(8, oRow)
	oCell.setformula(Found.getFormula()
	oRow = oRow + 1
	Found.setformula("")
   Found = oInCells.FindFirst(WCRoot)
   loop
   
Endif 


End Sub

Open in new window

0
 
Stan_GAuthor Commented:
After spending more time digging, finally found the info I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.