Solved No Result Calculated (Microsoft Excel 2003)

March 14, 2012 at 03:00:38
Specs: Windows XP
No result calculated after I input the function,why?
http://i.imgur.com/uvbao.jpg
All other dates are also inputted in the same way,
but only one(cell A40)calculate no result,
But the dates are inputted in the cell (Sheet1 A71)
What happened?
Thanks a lot

See More: No Result Calculated (Microsoft Excel 2003)

Report •


✔ Best Answer
March 14, 2012 at 08:30:29
KILRL:

You could try running this macro (in a back-up copy of your workbook) to see if that fixes it.


Sub FormulaFIxer()
  For Each r In ActiveSheet.UsedRange
    If r.HasFormula = False Then
      If Left(r.Value, 1) = "=" Then
         r.Formula = r.Value
      End If
    End If
  Next
End Sub

Stolen without permission from:

http://www.pcreview.co.uk/forums/fo...

The problem in that thread is not the same as yours, but the FormulaFixer macro might resolve your issue anyway.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 14, 2012 at 05:18:28
Perhaps the cell is stuck on Text formatting, regardless of what format you've chosen. It happens every now and then.

Try this:

Click on an empty cell and then Right-Click to choose Copy.

Then Right-Click on A40 and choose Paste Special...Add...OK

This will attempt to add 0 to the cell which might convert it to a number.

You might have to reformat it as a Date afterwards.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 14, 2012 at 05:31:16
Thanks For your reply:D
Tried, but it does not work:(
now I am wondering whether it was the file's problem or the software's problem..
As I double clicked on a cell(The date once,column A)
turns to editing,but when I choose any other cell,
the cell remains only showing the formula without calculations..
I've tried to set the cell as date,still don't work:(
A vid here for the problem:
http://youtu.be/ARTn8H4xFK4

Report •

#3
March 14, 2012 at 06:42:37
I'm not sure what's going on.

If I sent you an email address via PM would you be able to send me the file - with any confidential or sensitive data removed?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 14, 2012 at 06:55:27
Sorry it's related to some confidential stuffs..
But anyway thanks a lot for helping:)

Report •

#5
March 14, 2012 at 07:21:40
Can't tell from the pic's, but just an idea,
make sure you don't have a space in front of the equal sign.

MIKE

http://www.skeptic.com/


Report •

#6
March 14, 2012 at 07:44:23
If you have 2007, you can try a Diagnostic:

Running Microsoft Office Diagnostics from within Excel/Word

Click the Microsoft Office button
Click on the Excel/Word Options button to bring up the Excel Options dialog box (at the bottom of window)
Click on the Resources button in the left hand pane
Click on the Diagnose button in the right hand pane
Click Continue
Click Run Diagnostics

Running Microsoft Office Diagnostics from the Start Menu

Click on the Start button
Click All Programs
Click Microsoft Office folder
Click Microsoft Office Tools
Click Microsoft Office Diagnostics
Click Continue
Click Run Diagnostics

If you have 2000 / 2003 try doing a Detect & Repair:

On the Task Bar
Select Help
Select Detect & Repair

See if that helps.

MIKE

http://www.skeptic.com/


Report •

#7
March 14, 2012 at 08:25:17
Mike,

re: "make sure you don't have a space in front of the equal sign"

I don't know if you watched the video or not, but if there was a space before the equal sign, I don't think the dates would ever show up in the cells.

It's only after the OP edits the cells via double-click that the formula shows up and remains showing.

This problem sounds really familiar to me, but I don't recall the solution, which is why I wanted a copy of the sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 14, 2012 at 08:30:29
✔ Best Answer
KILRL:

You could try running this macro (in a back-up copy of your workbook) to see if that fixes it.


Sub FormulaFIxer()
  For Each r In ActiveSheet.UsedRange
    If r.HasFormula = False Then
      If Left(r.Value, 1) = "=" Then
         r.Formula = r.Value
      End If
    End If
  Next
End Sub

Stolen without permission from:

http://www.pcreview.co.uk/forums/fo...

The problem in that thread is not the same as yours, but the FormulaFixer macro might resolve your issue anyway.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
March 14, 2012 at 08:38:38
Just a thought...

What are the cells formatted as?

I found some info that seems to indicate that if the formula cells were/are formatted as General and the reference cells are formatted as Text, the cells with the formula will be forced into a Text format and Excel will not recognize the formula as a formula when you edit it.

The macro I suggested earlier should fix that.

Give it a try.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 14, 2012 at 08:43:44
DerbyDad03, I did not see the vid till after I posted my first reply,
after looking at the vid I suggested the Diagnostic/Repair.

Looks like something got turned on / off,
but can't figure out which switch.

MIKE

http://www.skeptic.com/


Report •

#11
March 14, 2012 at 08:48:34
DerbyDad03:

if the formula cells were/are formatted as General and the reference cells are formatted as Text, the cells with the formula will be forced into a Text format and Excel will not recognize the formula as a formula when you edit it.

That did it.

I was able to recreate the condition doing the above.

I would guess he is importing the data from an outside source,
which would account for the original dates being formatted as text.

MIKE

http://www.skeptic.com/


Report •

#12
March 14, 2012 at 09:07:25
So how did you fix it?

Was it a simple format change?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
March 14, 2012 at 09:31:31
Still working on that.

Changing the format of the target cell on Sheet2!A1 from TEXT to DATE
and double clicking, changes it to a regular date,
but the change does not propagate to Sheet1!A1, it remains with the formula displayed.

May have to reenter all the stuck formulas.

EDIT ADDED:

To get it to propagate across you have to double click the formula and it will change.


MIKE

http://www.skeptic.com/


Report •

#14
Report •

#15
March 14, 2012 at 10:13:44
Your macro works, but only after the dates have been converted from Text to Dates.

To get the Dates converted, found it to be easier to use a helper column:

On Sheet 2
Format Column A & B as Date

In column B do a =DATEVALUE(A1), drag down,
In column B do a Copy
In column B do a Paste / Special / Value
Copy Column B to Column A
Now Column A has dates that are really dates.

Then on Sheet 1 format Column A as Date
run the Macro

MIKE

http://www.skeptic.com/


Report •

#16
March 14, 2012 at 10:26:47
Of course, code could be added to the macro to do the DATEVALUE conversion and the formatting right in the cells. A one click fix...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
March 14, 2012 at 10:30:05
Here's another strange anomaly:

If you do:

In column B do a =DATEVALUE(A1), drag down,
In column B do a Copy
In column B do a Paste / Special / Value
CUT the values from Column B and then Paste to Column A

You get a #REF error on Sheet 1 even using the Macro.

MIKE

http://www.skeptic.com/


Report •

#18
March 14, 2012 at 10:46:15
Well, that's not really an anomaly, since an anomaly is a deviation from the norm.

What you are seeing isn't a deviation from the norm, it is the norm. It's just the way that Excel works.

When you do a Cut and Paste, the actual physical cell that you pasted the info into is deleted and replaced with the Cut cell. Since the original cell itself no longer exists, any formula that references that cell will be referencing a nonexistent item, thus the #REF error.

Strange...but true.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
March 15, 2012 at 05:04:32
Tried DerbyDad's macro,
first set all column A as date,
then run the macro,works!
Thanks So Much and everyone discussing about this.

Report •

Ask Question