Search

This macro searches records in a range to find a record that matches up to 3 criteria. The function itself returns true or false. If the first criteria one wants to match is in range column 0, for example, before calling the function, set c = 0 and r = "". If the record is found, the sheet column and row of the found record will be returned as well. This also does simple searches. (FindSheet is a function. It returns the name of a sheet that a given range is on.)

Function FindTriple(trange, tsheet, c, r, tname, tname2, offset2, tname3, offset3, n, go) as Boolean
'if c (or r) is a number, look for nth occurence in column c (or row r) of trange on tsheet
'with tname2 offset offset2 cols and tname3 offset offset3 cols
'columns and rows start at 0 ... c and r are initially range (not sheet) references
'before calling function, set r (or c) to "" to search all rows (or all columns)
'returns sheet (not range) col c and row r where found, or if not found first cell of searched range,
'or ... col and row of last cell where found (for n > 1)
'trange can be chosen as "" to search entire sheet
'if both trange and tsheet are "", current selection is searched
'tname2 or tname3 can be chosen as "" for simpler searches

FindTriple = 0
oDoc = ThisComponent

if oDoc.sheets.hasbyname(tsheet) then
oSheet = oDoc.sheets.getbyname(tsheet)
if oDoc.NamedRanges.hasByName(trange) then
oRange = oSheet.getcellrangebyname(trange)
else
oRange = oSheet
end if
else
if oDoc.NamedRanges.hasByName(trange) then
tsheet = FindSheet(trange,0)
oSheet = oDoc.sheets.getbyname(tsheet)
oRange = oSheet.getcellrangebyname(trange)
else
oSheet = oDoc.CurrentController.getActiveSheet
oRange = oDoc.getcurrentselection()
if isnull(oRange) then oRange = oSheet
end if
end if

with oRange.rangeaddress
c1 = .startcolumn
c2 = .endcolumn
r1 = .startrow
r2 = .endrow
end with

if isnumeric(c) then c1 = c1 + c : c2 = c1'to search in only one column
if isnumeric(r) then r1 = r1 + r : r2 = r1'to search in only one row
oRange = oSheet.getcellrangebyposition(c1, r1, c2, r2)
oCell = oSheet.getcellbyposition(c1, r1)
c = c1
r = r1
k = 0
if tname = oCell.string then gosub FoundOne

xSearchD = oRange.createSearchDescriptor()
With xSearchD
.SearchString = tname
.SearchCaseSensitive = false
.SearchWords = true
REM SearchWords forces the entire cell to contain only the search string
'from Andrew Pitonyak's ooDocument. He used bWholeWord instead of true
'.SearchWords = bWholeWord
End With

do
xFound = oCell
xFound = oRange.findNext(xFound, xSearchD)
if IsNull(xFound) then exit do
oCell = xFound
gosub FoundOne
loop
exit function

FoundOne:
c = oCell.rangeaddress.startcolumn
r = oCell.rangeaddress.startrow
temp2 = oSheet.getcellbyposition(c + offset2,r).string
temp3 = oSheet.getcellbyposition(c + offset3,r).string
if (len(tname2) < 2 or temp2 = tname2) _
and (len(tname3) < 2 or temp3 = tname3) then k = k + 1
If k = n Then
FindTriple = -1
If go Then
oCell = oSheet.getcellbyposition(c,r)
oDoc.getCurrentController.select(oCell)
End If
exit function
End If
Return
End Function