how to pass a range to a function; the starting cell is known, ending unknown

[jalea148]

The function definition looks something like

Public Function xyz(Rng as Range)​

If all the cells are defined, I can call it with

=xyz(C10:C55)
​

Each time xyz is required, the starting cell in the column is known, but the last defined cell in each column may change with new data. Cells before row10 and after the last one are always blank

Whenever the last cell is undefined in the call [i.e. if C55 were blank] then the cell shows #Value!.

 

[shg]

What does tthe function do -- does the range contain numbers or text?

 

[jalea148]

The function definition looks something like

Public Function xyz(Rng as Range)​

If all the cells are defined, I can call it with

=xyz(C10:C55)
​

Each time xyz is required, the starting cell in the column is known, but the last defined cell in each column may change with new data. Cells before row10 and after the last one are always blank

Whenever the last cell is undefined in the call [i.e. if C55 were blank] then the cell shows #Value!.

Another thought ==>> let's assume C10:C55 is a range filled with dates. There are 46 dates. If

=Countif(C10:C5000,,"> 12/31/1900") + 9

is put in C9 then I would expect the following to work:

= xyz(C10:C & C9)

BUT IT FAILS. {This type of expression works in VBA in the form Range("C10:C" & C9.value}

 

[shg]

=xyz(C10:INDEX(C10:C5000, MATCH(9E+307, C10:C5000)))

 

[jalea148]

=xyz(C10:INDEX(C10:C5000, MATCH(9E+307, C10:C5000)))

I'm not sure what 9E or 307 represents. Index/Match appears to return a value from a table rather than the cell location in that table.