function to modify other cells

[rat59man]

I am trying to write a function to modify a group of cells. As an example in
cell C1 I insert "=myfunction(1)" and in cells A1, B1, D1, E1 I want the
values of 3, 5, 7, and 11 placed.

At the assignment I get a 1004 error number returned.

My thought is that functions are not allowed to modify cells directly. Is
this correct?

 

[Chip Pearson]

A function called from a worksheet cell cannot change the value of any
other cell. It can only return a value to the cell(s) from which it
was called. The reason for this limitation is that Excel must keep
track of which cells are dependent on which other cells so it can
calculate in the proper order. Excel can't determine what VBA code
might do, so it disallows code from changing other cells.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Thomlinson]

Just a quick note. Even if you could write a function that modified other
cell it would be a really bad idea. Lets assume that we could. In Cell A1 you
have the value 3. Did someone input that value there? Did a function put it
there? If so which function? If 2 functions can modify the same cell which
one put a value there last? Any spreadsheet with those types of functions
would be impossible to debug.