How to get a Range of cells as an function argument

Question

I'm trying to make a simple macro that is supposed to loop through a given range of cells and return a cell next to a cell matching some content.

From some forums I found a line that is supposed to work for cell objects:

someCell.Offset(0,colIndex)

But from debugging I can see that the input data from the selection is a set of Variant/String and not a "cell" object.

I there a way for my function to get a Range of cells instead of Variant/Strings?

Also my complete code:

Function RVLOOKUP(target, criteriaCellRange, colIndex)

    Dim found as Boolean
    Dim resultCell as Variant

    For Each cell In criteriaCellRange
        matchPosition = instr(target,cell)
        If matchPosition > 0 Then
            found = True
            resultCell = cell
            Exit For
        end if
    Next cell

    If found Then
        ' Here resultCell seems to be just a String not an actual cell object
        RVLOOKUP = resultCell.Offset(0,colIndex)
    else
        RVLOOKUP = "#NoMatch"
    end if

End Function

UPDATED: The code is suppose to find a cell from criteriaCellRange that is an exact or partial match of a text in cell target and return the cell with an horizontal offset of colIndex from matching cell from criteriaCellRange. So basically a VLOOKUP that also matches partial text.

So... HERE we have the same problem, and the answer there confirms my suspicions:

When passing a cell range as a parameter of a function, I believe the function receives either strings or values. The zero is just the value of the blank cell. Without seeing your code, it is hard to suggest a solution to your problem.

BTW I'm using Libreoffice 5.4.4.2.

Answer 1

Just a couple of tweaks:

Function RVLOOKUP(target, criteriaCellRange, colIndex)

    Dim found As Boolean
    Dim resultCell As Range

    For Each cell In criteriaCellRange
        matchPosition = InStr(cell, target)
        If matchPosition > 0 Then
            found = True
            Set resultCell = cell
            Exit For
        End If
    Next cell

    If found Then
        ' Here resultCell seems to be just a String not an actual cell object
        RVLOOKUP = resultCell.Offset(0, colIndex)
    Else
        RVLOOKUP = "#NoMatch"
    End If

End Function

I reverse the InStr() arguments and made resultCell a Range

Answer 2

From the OO documentation:

Arguments are passed as values

Arguments passed to a macro from Calc are always values. It is not possible to know what cells, if any, are used. For example, =PositiveSum(A3) passes the value of cell A3, and PositiveSum has no way of knowing that cell A3 was used. If you must know which cells are referenced rather than the values in the cells, pass the range as a string, parse the string, and obtain the values in the referenced cells.

So what I wanna achieve is not possible. :(