Function () that manipulates other cells
 

Howdy Ya'll

I'm trying to write a formula that, when input in cell 1, will change
propeties or values of a second cell or range. Whenever I try to
manipulate the contents of a second range, the function always
terminates at that line (with no error). The conceptual code is as
follows:

Function misc_function(input1 As Range, input2 As Range)

Worksheets("worksheet2").Range(a2).Formula = input1
Worksheets("worksheet2").Range(a3).Formula = input2

misc_function = Worksheets("worksheet2").Range(a4).Formula

MsgBox ("It's Over!")

End Function

I've tried variations on this that change the active selection with no
luck either. Is there a way to do this sort of thing? The idea is that
someone can change the formulas or calculations on "worksheet 2" and
thus the output of the function without having to write any VBA (very
helpful for non-VBA literate users). Thanks for your help.

Nathan


--
Nate_Dogg_Bry
------------------------------------------------------------------------
Nate_Dogg_Bry's Profile: http://www.excelforum.com/member.php...o&userid=27457
View this thread: http://www.excelforum.com/showthread...hreadid=469707

Function () that manipulates other cells
 

A function procedure is not allowed to alter other cells in this way.
Try putting your code into a Sub procedure and link it to a button or
event.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=469707

Function () that manipulates other cells
 

A function can only change one cell. A function in conjunction with a macro
can cause several cells to change. One method is to DIM static variables
(outside the function) and have the function modify them when it is executed.
Create a worksheet change or calculate event macro to detect the function's
execution. The event macro could then modify other cells in the worksheet.
--
Gary''s Student


"Nate_Dogg_Bry" wrote:


Howdy Ya'll

I'm trying to write a formula that, when input in cell 1, will change
propeties or values of a second cell or range. Whenever I try to
manipulate the contents of a second range, the function always
terminates at that line (with no error). The conceptual code is as
follows:

Function misc_function(input1 As Range, input2 As Range)

Worksheets("worksheet2").Range(a2).Formula = input1
Worksheets("worksheet2").Range(a3).Formula = input2

misc_function = Worksheets("worksheet2").Range(a4).Formula

MsgBox ("It's Over!")

End Function

I've tried variations on this that change the active selection with no
luck either. Is there a way to do this sort of thing? The idea is that
someone can change the formulas or calculations on "worksheet 2" and
thus the output of the function without having to write any VBA (very
helpful for non-VBA literate users). Thanks for your help.

Nathan


--
Nate_Dogg_Bry
------------------------------------------------------------------------
Nate_Dogg_Bry's Profile: http://www.excelforum.com/member.php...o&userid=27457
View this thread: http://www.excelforum.com/showthread...hreadid=469707

Function () that manipulates other cells
 

If you are calling it from a worksheet you can't. Worksheet functions cannot
change cell attributes.

You could try worksheet event code, but you would need to define which cells
trigger the change.

--
HTH

Bob Phillips

"Nate_Dogg_Bry"
wrote in message
news:Nate_Dogg_Bry.1vqbqa_1127340326.8218@excelfor um-nospam.com...

Howdy Ya'll

I'm trying to write a formula that, when input in cell 1, will change
propeties or values of a second cell or range. Whenever I try to
manipulate the contents of a second range, the function always
terminates at that line (with no error). The conceptual code is as
follows:

Function misc_function(input1 As Range, input2 As Range)

Worksheets("worksheet2").Range(a2).Formula = input1
Worksheets("worksheet2").Range(a3).Formula = input2

misc_function = Worksheets("worksheet2").Range(a4).Formula

MsgBox ("It's Over!")

End Function

I've tried variations on this that change the active selection with no
luck either. Is there a way to do this sort of thing? The idea is that
someone can change the formulas or calculations on "worksheet 2" and
thus the output of the function without having to write any VBA (very
helpful for non-VBA literate users). Thanks for your help.

Nathan


--
Nate_Dogg_Bry
------------------------------------------------------------------------
Nate_Dogg_Bry's Profile:
http://www.excelforum.com/member.php...o&userid=27457
View this thread: http://www.excelforum.com/showthread...hreadid=469707

Function () that manipulates other cells
 

Thank's Ya'll,

I guess that has to do with events order in which excel recalculates a
spreadsheet, eh?

Nathan


--
Nate_Dogg_Bry
------------------------------------------------------------------------
Nate_Dogg_Bry's Profile: http://www.excelforum.com/member.php...o&userid=27457
View this thread: http://www.excelforum.com/showthread...hreadid=469707