Excel Function not Evaluating to a Result

[David Cook]

Hi,

A colleague of mine has a spreadsheet containing a number of formulas which
reference cells in another sheet within the same workbook, so the cells
contain for example
='Reference Data'!A3

He populates the sheet using VBA code inserting the functions into the
appropriate cells, and on initial creation this works fine and all the cells
show the correct result.

i.e. If the Reference Data sheet at cell A3 contains "Fred Bloggs" then
"Fred Bloggs" is displayed in the cell.

However, if he edits the cell function to change say the !A3 to !A4, then
instead of the cell displaying the new result, it just displays the function,
i.e. ='Reference Data'!A4
This happens even if he changes it back to !A3

We've both been racking our brains trying to figure what the heck is going
on as we cannot see why this would happen, so I'm asking the Experts out
there if they might know why, and what we can do to stop it behaving so oddly.

Many thanks in advance.

David

 

[JLatham]

The cell has become formatted as text. Change the formatting to 'General'.

He can probably make the change in his code; a simplistic example of how to
do that would be:

where he now has something like this setting a formula:
Range("G2").Formula = "=SomeSheetName!B55"

add a line before that, as:
Range("G2").NumberFormat = "General"
Range("G2").Formula = "=SomeSheetName!B55"