You can't use a formula to change the value in another cell.

You could use a Sub (subroutine, not Function) that does the work. In fact,
excel has some builtin events that you may be able to tie into.

But it depends on what you want. You could tie into the worksheet_change event
if the user is typing something that causes the change. You could tie into the
worksheet_calculate if a formula reevaluates and causes the change.

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

magix wrote:

Hi Dave,

I think my scenario fall under your warning statement.
It doens't work with the suggestion.
I just want to set a value in another cell. You said ="A function called
from a cell on a worksheet can't change the value in
another cell."

Then how can I change the value in another cell after this function has been
executed in a worksheet cell ?

Thanks.

Regards,
Magix

"Dave Peterson" wrote in message
...
I would try to be as specific as possible:

activesheet.Range("A1").value = S1
or
worksheets("sheet999").Range("A1").value = S1
or
activeworkbook.worksheets("sheet999").Range("A1"). value = S1
or
someworkbookvariablehere.worksheets("sheet999").Ra nge("A1").value = S1

Just a word of warning.

If you're thinking about using this function in a worksheet cell, then it
won't
work. A function called from a cell on a worksheet can't change the value
in
another cell. Essentially, it can only return a value to the cell with
the
formula.


magix wrote:

Hi,

If I have VBA Function, what is the correct statement to insert a value
into
a particular cell, let say cell A1 ?

Function ABC ((S1 As String) As String

' how can I set the value at particular cell ?
Range("A1").value = S1
...
...
End Function

is this Range("A1").value = S1 correct ? or Application.Range("A1").value
=
S1 ? or... ?

--

Dave Peterson

--

Dave Peterson