If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing worksheet cells from within a function
I have placed "=Test(123)" in cell B2. The function is supposed to return
the value 200 in cell B2 and put the value of 100 in cell A1, but instead returns the "#VALUE!" error and does noting to cell A1. However, when the same function is called from within a sub (Test2), it works properly. How can I get the function to work properly when called from a worksheet? The function and subs appear below: Function Test(X As Double) Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 Test = 200 End Function Sub Test1() Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 End Sub Sub Test2() MsgBox (Test(123)) End Sub |
#2
|
|||
|
|||
Changing worksheet cells from within a function
A UDF called from a worksheet can't change a different cell. It can return a
value to the cell with the formula--and that's about all. A function called not originated from a UDF in a cell can modify other cells. James4U2enjoy wrote: I have placed "=Test(123)" in cell B2. The function is supposed to return the value 200 in cell B2 and put the value of 100 in cell A1, but instead returns the "#VALUE!" error and does noting to cell A1. However, when the same function is called from within a sub (Test2), it works properly. How can I get the function to work properly when called from a worksheet? The function and subs appear below: Function Test(X As Double) Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 Test = 200 End Function Sub Test1() Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 End Sub Sub Test2() MsgBox (Test(123)) End Sub -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Expanding conditional formating with reference cells changing | CCoop | General Discussion | 2 | May 4th, 2005 02:36 PM |
Unlocking Cells when a worksheet is protected... | racmb1975 | General Discussion | 2 | May 3rd, 2005 08:57 PM |
embedding worksheet cells in another worksheet | SCW | General Discussion | 2 | April 25th, 2005 04:29 PM |
Trace Auditing Pointing to Dependent Cells in a Different Worksheet | cayo0734 | General Discussion | 1 | October 2nd, 2004 04:30 PM |
inserting worksheet name in a cell using function | GUS | Worksheet Functions | 1 | May 25th, 2004 01:40 AM |