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 ??/


Kiwi Trader Guy wrote:

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

set rng = application.Caller
rw = rng.row
col = rng.column

--
Regards,
Tom Ogilvy



"Kiwi Trader Guy" wrote in message
...
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

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



Tom Ogilvy wrote:

set rng = application.Caller
rw = rng.row
col = rng.column

--
Regards,
Tom Ogilvy

"Kiwi Trader Guy" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"Kiwi Trader Guy" wrote in message
...
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



Tom Ogilvy wrote:

set rng = application.Caller
rw = rng.row
col = rng.column

--
Regards,
Tom Ogilvy

"Kiwi Trader Guy" wrote in message
...
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

Tom

You're a champion and a generous human being.

Problems solved.

Many thanks.


A.


Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Kiwi Trader Guy" wrote in message
...
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



Tom Ogilvy wrote:

set rng = application.Caller
rw = rng.row
col = rng.column

--
Regards,
Tom Ogilvy

"Kiwi Trader Guy" wrote in message
...
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