vba code overwrites formulae?
 

I have written vba code that reads in data from another workbook into a row.
To perform calculations on that data, I have six other rows referencing that
row. The calculations are by way of formula entered into the referencing
cells.

For example:

Cell A1 reads in "N" from other worksheet. Cell A1.offset(1,0) contains a
formula that returns the value 8 from looking at that cell.

Running the code to read in the data the first time works fine. Everything
updated.

Problem is, that running it a second time, the formulae do not execute - old
values remain in the referencing cells. For example:

On second run
Cell A1 reads in "X" from other worksheet. Cell A1.offset(1,0) containing
the formula retains the value 8, does not see the update.

Have tried saving and closing to refresh. Have tried "activating" the cell
in the code (overwrites the formula!) and "selecting". No go.

If I click in the referencing cell, then click in the formula bar and exit
the cell, formula fires.

How can I ensure formula recalculate on re-reading the data?

Many TIA's


Matilda

vba code overwrites formulae?
 

Check that you don't have manual calculation set, ToolsOptionsCalculate.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matilda" wrote in message
...
I have written vba code that reads in data from another workbook into a
row.
To perform calculations on that data, I have six other rows referencing
that
row. The calculations are by way of formula entered into the referencing
cells.

For example:

Cell A1 reads in "N" from other worksheet. Cell A1.offset(1,0) contains a
formula that returns the value 8 from looking at that cell.

Running the code to read in the data the first time works fine. Everything
updated.

Problem is, that running it a second time, the formulae do not execute -
old
values remain in the referencing cells. For example:

On second run
Cell A1 reads in "X" from other worksheet. Cell A1.offset(1,0) containing
the formula retains the value 8, does not see the update.

Have tried saving and closing to refresh. Have tried "activating" the cell
in the code (overwrites the formula!) and "selecting". No go.

If I click in the referencing cell, then click in the formula bar and exit
the cell, formula fires.

How can I ensure formula recalculate on re-reading the data?

Many TIA's


Matilda

vba code overwrites formulae?
 

Bingo!

Thanks, Bob

"Bob Phillips" wrote:

Check that you don't have manual calculation set, ToolsOptionsCalculate.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Matilda" wrote in message
...
I have written vba code that reads in data from another workbook into a
row.
To perform calculations on that data, I have six other rows referencing
that
row. The calculations are by way of formula entered into the referencing
cells.

For example:

Cell A1 reads in "N" from other worksheet. Cell A1.offset(1,0) contains a
formula that returns the value 8 from looking at that cell.

Running the code to read in the data the first time works fine. Everything
updated.

Problem is, that running it a second time, the formulae do not execute -
old
values remain in the referencing cells. For example:

On second run
Cell A1 reads in "X" from other worksheet. Cell A1.offset(1,0) containing
the formula retains the value 8, does not see the update.

Have tried saving and closing to refresh. Have tried "activating" the cell
in the code (overwrites the formula!) and "selecting". No go.

If I click in the referencing cell, then click in the formula bar and exit
the cell, formula fires.

How can I ensure formula recalculate on re-reading the data?

Many TIA's


Matilda