Help: How to get function return result in the cell next to it

[yurble_vn]

I intend to write a function that can return a set of value next to cell it's called.

For example, I want to input the function at cell A1, and the result will be put in A2, A3...A7. But I don't know how to identify the current cell that I input the function. How to get out that, the cell that function is input in is A1 (in my case)??

Help please

[unmarkedhelicopter]

Good luck with that !

[Norie]

You can't use functions to put values in other cells.

[mdmackillop]

You can use an Event to write values/formulae into other cells, but not a function.
eg
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
With Target
.Offset(, 1) = Target * 2
.Offset(, 2).FormulaR1C1 = "=R1C1*2"
End With
End If
End Sub

[yurble_vn]

I can not change the cells even with a sub?

I mean, can I call a sub to change other cell.??

[Norie]

Yes you can, but not using a function.

What you need is probably something like mdmackillop has posted.

Perhaps you could give us more information of what you actually want to achieve?

[yurble_vn]

What I intend to do is:

Get a data range, do some analasis on it, then return a list of result begin from the cell I input function. It's quite clear, cause i remember some statistics add-in can do it.

Just call a function, then it will return a table of results.

[mdmackillop]

You need to be clear about the difference between a Sub and a Function. A Sub or an Array Function might do this; a standard function in one cell will not.

[yurble_vn]

Thanks, get clearer now.

By the way, can we call sub from cell?

[unmarkedhelicopter]

No