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.

Solution 1

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. :(

Solution 2

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

Comments

[G-Man Says 'Reinstate Monica']

(1) How is your function called?   Please do not respond in comments; edit your question to make it clearer and more complete.  (2) You should make sure that found is initialized (I’d suggest = False) before the loop.  (3) What is foundCell?   You mention it in a context-less line of code, and then you use it in the function without ever setting it.  (4) What happens?

[danizmax]

@G-Man Sorry the foundCell was a typo its suppose to be resultCell. The code above is a working code except for the line below the comment.

[danizmax]

Sorry but reversing the InStr() is wrong, my code does work as intended till the commented line, also I'm getting "BASIC syntax error. Unknown data type Range." for line "Dim resultCell As Range". I've update the querstion with an explanation what I wanna do.

[Hannu]

"pass the range as a string, parse the string, and obtain the values in the referenced cells." - i.e. pass the CELL address, not the values, as parameter.

[danizmax]

@Hannu For being so long in computer business you should really start reading correctly and not down vote without thinking. What you quoted in your comment isn't what I want.

[Hannu]

Quick assumption from your side, I commented only. The comment tells you the path to go to get it done..

[Hannu]

NOTE: What you have here is "not an answer", so the down vote is likely to be someone's response to that fact. Note also the "close(1)", probably the same source.

[danizmax]

@Hannu I see, sorry then, I can flag it as an answer tomorrow for some reason, probably because of my low reputation on this SO site. Anyways, getting the range as a string is not enough because I can't reuse the function the way I could have if there was a way.