[Solved] Return column and row using Calc search in a macro

[grnhorn]

I would like to return the row and column using Calc's Search & Replace as a macro. The following code will find the string and select the cell, but it only returns the row and column of the selected cell before the search began. It will not return the row/column where the find is located.

Thanks...

Sub Search()

dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
otSearchBox = oDBDlg.GetControl("tSearchBox") ' Get textfield object
args1(10).Value = otSearchBox.Text
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = ""
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 0
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())

oSelectedCells = ThisComponent.CurrentSelection
oActiveCell = oSelectedCells.CellAddress
iRow = oActiveCell.Row
iCol = oActiveCell.Column

End Sub

[JohnV]

Code: Select all

Sub Find
sAns = InputBox("Enter what to find below.")
If sAns = "" then End 'Blank entry or Cancel clicked.
oDoc = ThisComponent
oCell = oDoc.CurrentSelection 'Get active sheet index.
index = oCell.CellAddress.Sheet
oSheet = oDoc.getSheets.getByIndex(Index)'Get active sheet. 
FandR = oSheet.createSearchDescriptor 'Set up find and replace.
FandR.setSearchString(sAns)
'FandR.SearchWords = true 'Entire cell must match.
oCell = oSheet.findFirst(FandR)
If not isNull(oCell) then
 MsgBox "Row = " & oCell.CellAddress.Row & Chr(13) & "Column = " & oCell.CellAddress.Column
 Else MsgBox "Not Found!"
EndIf
End Sub

[jolumafe]

And, with your permission sir, a working function:

Code: Select all

Sub find_coords(strString as string, objSheet as object) as string
	dim oCell as object
	dim index as long
	dim FandR as object
	If strString = "" then End 'Blank entry or Cancel clicked.
	oCell=objSheet.getCellByPosition(1, 1)
	index = oCell.CellAddress.Sheet
	FandR = objSheet.createSearchDescriptor 'Set up find and replace.
	FandR.setSearchString(strString)
	oCell = objSheet.findFirst(FandR)
	If not isNull(oCell) then 
		find = oCell.CellAddress.Row & "," & oCell.CellAddress.Column
	Else 
		find=""
	EndIf
End Sub

Sub example
    dim oCoords as object
    oCoords=split(find_coords("whattosearch",oMysheet),",")
    'oCoords(0)=row  ; oCoords(1)=column
End sub