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

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

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

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

Relative cell value in function
 

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

Relative cell value in function
 

That is to the right?

--

Regards,
Nigel




wrote in message
...
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

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

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

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