ForumulaArrays - help needed.

  • Thread starter Thread starter Kiwi Trader Guy
  • Start date Start date
K

Kiwi Trader Guy

Hi

I want to write a simple FormulaArray type function that will take a
Range as input, and return a value subect to where the cell is in
reference to the input range.

i.e. say a it was just to reverse the input

1 -> 4
2 -> 3
3 -> 2
4 -> 1

This is a trivial example, but what gets me is know where i am in the
destination cell so that I can return the right value from the input
range.

Does that make sense?


Function foo(Rin As Range)

' foo = Rin.Row
'foo = Rin.Rows.Count

'BUT what is the row/cell value of the cell that called me??????

End Function
 
I think what i need is an example of the Minverse function ??/
 
Thanks for your response.

Unfortunately this only returned the first cell of the selection, and as I
was using a CSE (CNTL-SHIFT-ENTER) function, this was just the top of the
range of the column that was selected.

Perhaps I need to return a range or somehow set all the values in the range
in the function - but I don't quite understand how these CSE functions are
supposed to work.

Would be good to see the function definition of say MINVERSE as this does
it.

Thanks in advance
 
apparently your confused.

Public Function Test(rng As Range)
Set rng1 = Application.Caller
Debug.Print rng1.Address
ReDim varr(1 To rng1.Rows.Count, _
1 To rng1.Columns.Count)
For i = 1 To UBound(varr, 1)
For j = 1 To UBound(varr, 2)
varr(i, j) = rng1(i, j).Address
Next
Next
Test = varr
End Function

displayed the address of each cell in that addres and in the immediate
window (the debug.print statement) it displayed

$G$4:$J$14

This definitely was more than one cell and included the entire range where
the formula was entered.


Most people won't understand CSE. that is some cutesy name dreamed up in
the Mr. Excel forum. The appropriate name is array formula. Better to
communicate in common terminology.
 
Tom

You're a champion and a generous human being.

Problems solved.

Many thanks.


A.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why does variant not need set? 2
VBA set thick borders around non empty cells in range 0
Convert code to lookup three criteria return fourth 15
Find sometimes ignores Application.FindFormat 3
Operations on union of ranges 2
Loop thru xx No. of sheets until the four strValue combo's are found 6
Copy Array to Array, how to and should I? 13
Using functions in string formatted cells 4

Back
Top