Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wHAT AM i DOING wrong
'Public Function DisplayCellFunction(cellID1 As String, cellID2 As String) Public Function DisplayCellFunction(cellID1 As Integer, cellID2 As Integer) 'Copy formulas to the adjacent cell for visual verification 'Range(cellID1) = "'" & Range(cellID2).Formula Range("F26") = "'" & Range("d26").Formula End Function In the workbook I enter =DisplayCellFunction(F26, D26) I get a #Name Error =DisplayCellFunction("F26", "D26") I get a #Name Error =DisplayCellFunction(6, 6) I get a #Value! Error |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wHAT AM i DOING wrong
You can't change cells from within a function; you can just replace the call
of the function by a result. In none of the examples do you assign a value to the function; somewhere there should be a DisplayCellFunction = Someresult or something similar In example one you use F26 as a name, and/or it is not an integer In example 2 you use text arguments, although they are declared Integers Example 3 has no result assigned -- Kind regards, Niek Otten "DMB" wrote in message ... 'Public Function C(cellID1 As String, cellID2 As String) Public Function DisplayCellFunction(cellID1 As Integer, cellID2 As Integer) 'Copy formulas to the adjacent cell for visual verification 'Range(cellID1) = "'" & Range(cellID2).Formula Range("F26") = "'" & Range("d26").Formula End Function In the workbook I enter =DisplayCellFunction(F26, D26) I get a #Name Error =DisplayCellFunction("F26", "D26") I get a #Name Error =DisplayCellFunction(6, 6) I get a #Value! Error |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wHAT AM i DOING wrong
Niek's right; the function must return a result which will appear (or be
used in) the cell from which it was called (much like SUM; it doesn't post its results to another cell, but rather to the cell in which you use the function). If I understand your intent, you could do something like this: Function DisplayCellFunction(Cell1 As String) As String DisplayCellFunction = Range(Cell1).Formula End Function So if you want to see the formula that's in D26, then in cell F26, enter =DisplayCellFunction("D26") "DMB" wrote: 'Public Function DisplayCellFunction(cellID1 As String, cellID2 As String) Public Function DisplayCellFunction(cellID1 As Integer, cellID2 As Integer) 'Copy formulas to the adjacent cell for visual verification 'Range(cellID1) = "'" & Range(cellID2).Formula Range("F26") = "'" & Range("d26").Formula End Function In the workbook I enter =DisplayCellFunction(F26, D26) I get a #Name Error =DisplayCellFunction("F26", "D26") I get a #Name Error =DisplayCellFunction(6, 6) I get a #Value! Error |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wHAT AM i DOING wrong
dmb Wrote: 'Public Function DisplayCellFunction(cellID1 As String, cellID2 As String) Public Function DisplayCellFunction(cellID1 As Integer, cellID2 As Integer) 'Copy formulas to the adjacent cell for visual verification 'Range(cellID1) = "'" & Range(cellID2).Formula Range("F26") = "'" & Range("d26").Formula End Function In the workbook I enter =DisplayCellFunction(F26, D26) I get a #Name Error =DisplayCellFunction("F26", "D26") I get a #Name Error =DisplayCellFunction(6, 6) I get a #Value! Error A Function procedure cannot change the value of another cell. You have to use a Sub procedure to do that. You might try to entering a function in the cell where you want the formula from the adjacent cell displayed. Try this code for that function Code: -------------------- Public Function DisplayCellFunction(CellID As String) As String Application.Volatile DisplayCellFunction = "'" & Range(CellID).Formula End Function -------------------- Use this syntax: =DisplayCellFunction("A1") The Application.Volatile Statement recalculates the value of the function any time data changes on the sheet. i.e. if you were to change the formula that cell references, unless you have Application.Volatile, it won't change the result of your function. -- Brassman ------------------------------------------------------------------------ Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290 View this thread: http://www.excelforum.com/showthread...hreadid=495832 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wrong year | Excel Worksheet Functions | |||
Password box to remain open after wrong input | Excel Discussion (Misc queries) | |||
after entering certain number of times wrong password file can cur | Excel Discussion (Misc queries) | |||
Wrong date on my posts. | Excel Discussion (Misc queries) | |||
What is wrong with this =AVERAGE(IF formula? | Excel Worksheet Functions |