| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative cell value in function
I have a large spreadsheet the uses a function to calculate the cell value.
I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
I think you might need to run the function to determine the value, I presume
you mean before it is applied to the cell. Without seeing the function, you could assign to a variable then evaluate that, then update cell ... maybe? Pseudo code....... dim myResult variable myResult = myFunction(myParameters) etc... if OK then myCell = myResult The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1) returns value in A1 -- Regards, Nigel "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
Thanks Nigel, but that is exactly what is not working. Picture if you will a
grid of 40 columns and 4000 rows. In each of these cells the value of the cell is based on a function MyFunction(parameters). If I right click in the grid and choose refresh the cells are all sequentially correctly updated using the function. However if I put the following into the code Debug.Print ActiveCell.Value, ActiveCell.Row, ActiveCell.Column it only returns the details of A1 each time the function is run. It appears that although the value in the given cell is updated during the AutoCalculation, the cell is never the ActiveCell. I guess looking at the problem from a different way how do I make the cell currently being updated the ActiveCell when the apparent ActiveCell doesn't seem to change? H "Nigel" wrote: I think you might need to run the function to determine the value, I presume you mean before it is applied to the cell. Without seeing the function, you could assign to a variable then evaluate that, then update cell ... maybe? Pseudo code....... dim myResult variable myResult = myFunction(myParameters) etc... if OK then myCell = myResult The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1) returns value in A1 -- Regards, Nigel "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
The function is much to big to put in here. It does however use
semi-absolute references to determine the parameters for the function. The function call and parameter list is CalculatedBenefit($A47,$D47,$E47,$F47,$G47,$H47,VL OOKUP($A47,'MeterReading Lookup'!$A$7:$AZ$192,O$3),O$6,$L47,$K47,$N47,$M47) . Now this works but occasionally the result of the VLOOKUP returns a zero due to missing data in the lookup table (I'm using live data so there is no possibility of interpolating a value). I simply want the function to return the value in the cell to the left if the VLOOKUP is zero. I think I've just answered my own question. I would need to do it using an if in the cell function call. If lookup=0 then cell to the left otherwise call function. By George I think he's got it. Thanks for your help "Nigel" wrote: ActiveCell is the currently activated cell, the Function does not need to activate a cell to update it's value, so using this construct is erroneous, as you have discovered. The best option would be to post the Function code, the Cell referencing / assignment can then be used to intercept the cell value. Post your code. -- Regards, Nigel "Humphrey" wrote in message ... Thanks Nigel, but that is exactly what is not working. Picture if you will a grid of 40 columns and 4000 rows. In each of these cells the value of the cell is based on a function MyFunction(parameters). If I right click in the grid and choose refresh the cells are all sequentially correctly updated using the function. However if I put the following into the code Debug.Print ActiveCell.Value, ActiveCell.Row, ActiveCell.Column it only returns the details of A1 each time the function is run. It appears that although the value in the given cell is updated during the AutoCalculation, the cell is never the ActiveCell. I guess looking at the problem from a different way how do I make the cell currently being updated the ActiveCell when the apparent ActiveCell doesn't seem to change? H "Nigel" wrote: I think you might need to run the function to determine the value, I presume you mean before it is applied to the cell. Without seeing the function, you could assign to a variable then evaluate that, then update cell ... maybe? Pseudo code....... dim myResult variable myResult = myFunction(myParameters) etc... if OK then myCell = myResult The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1) returns value in A1 -- Regards, Nigel "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
I often discover a solution by discussion, rather than a formal problem
solving session. So a question "is it possible to determine the value in the cell before the function is run" is so close to the answer but interpreted completely differently - fascinating!! Good luck. -- Regards, Nigel "Humphrey" wrote in message ... The function is much to big to put in here. It does however use semi-absolute references to determine the parameters for the function. The function call and parameter list is CalculatedBenefit($A47,$D47,$E47,$F47,$G47,$H47,VL OOKUP($A47,'MeterReading Lookup'!$A$7:$AZ$192,O$3),O$6,$L47,$K47,$N47,$M47) . Now this works but occasionally the result of the VLOOKUP returns a zero due to missing data in the lookup table (I'm using live data so there is no possibility of interpolating a value). I simply want the function to return the value in the cell to the left if the VLOOKUP is zero. I think I've just answered my own question. I would need to do it using an if in the cell function call. If lookup=0 then cell to the left otherwise call function. By George I think he's got it. Thanks for your help "Nigel" wrote: ActiveCell is the currently activated cell, the Function does not need to activate a cell to update it's value, so using this construct is erroneous, as you have discovered. The best option would be to post the Function code, the Cell referencing / assignment can then be used to intercept the cell value. Post your code. -- Regards, Nigel "Humphrey" wrote in message ... Thanks Nigel, but that is exactly what is not working. Picture if you will a grid of 40 columns and 4000 rows. In each of these cells the value of the cell is based on a function MyFunction(parameters). If I right click in the grid and choose refresh the cells are all sequentially correctly updated using the function. However if I put the following into the code Debug.Print ActiveCell.Value, ActiveCell.Row, ActiveCell.Column it only returns the details of A1 each time the function is run. It appears that although the value in the given cell is updated during the AutoCalculation, the cell is never the ActiveCell. I guess looking at the problem from a different way how do I make the cell currently being updated the ActiveCell when the apparent ActiveCell doesn't seem to change? H "Nigel" wrote: I think you might need to run the function to determine the value, I presume you mean before it is applied to the cell. Without seeing the function, you could assign to a variable then evaluate that, then update cell ... maybe? Pseudo code....... dim myResult variable myResult = myFunction(myParameters) etc... if OK then myCell = myResult The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1) returns value in A1 -- Regards, Nigel "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
On 1 Feb, 07:30, "Nigel" wrote:
I think you might need to run the function to determine the value, I presume you mean before it is applied to the cell. *Without seeing the function, you could assign to a variable then evaluate that, then update cell ... maybe? Pseudo code....... dim myResult variable myResult = myFunction(myParameters) etc... if OK then myCell = myResult The cell to the left is ActiveCell.Offset(0,-1).Value or =OFFSET(A2,0-1) returns value in A1 -- Regards, Nigel "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. *Any thoughts? ActiveCell.Offset(0,1).Value |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Relative cell value in function
You can use Application.caller to return a range reference to the cell that
contains the function, but you cannot get its value in any straightforward way. One possible way is to use Application.Caller.Text, but that returns the formatted value rather than the actual value (could return ###### for instance) so is very dependent on the user formatting etc. To get the cell one to the left you could use OFFSET(application.caller,-1,0,1,1) but this would require you to make the function volatile (Application.Volatile), which could slow down recalculation. A better way would be to pass the cell to the left as a parameter to the function. You can use ISEMPTY() to determine if an input parameter has not yet been calculated. Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Humphrey" wrote in message ... I have a large spreadsheet the uses a function to calculate the cell value. I have two questions; * is it possible to determine the value in the cell before the function is run? * is it possible to determine the value in the cell immediately to the left? I've tried using ActiveCell.Offset but the result is always coming back as Empty. Any thoughts? H |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative VBA function | Excel Programming | |||
| Relative addressing with INDIRECT function | Excel Programming | |||
| Split function to get a relative path | Excel Programming | |||
| Get Relative Data from result of MIN function | Excel Programming | |||
| Indirect function - relative reference | Excel Programming | |||
Hybrid Mode
