Formula in Excel does not work until after I double-click inside c

[raaaabert]

I get this alot, and Ive searched everywhere for the answer.

No macros or hidden sheets active.
Manual calculation is not on, it is set on Automatic.
I've tried F9 for posterity, with nothing happening.
I've tried CTRL-ALT-SHIFT-F9 also nothing.

The cells Im working on have been imported from an external source, ie they
are not hand entered but I dont think this is the issue.

They are custom format though, i.e. h:mm:ss.
Once I double-click on the cell and hit return the formula works but never
before.

PLEASE HELP!!!!

 

[Simon Lloyd]

I get this alot, and Ive searched everywhere for the answer.

No macros or hidden sheets active.
Manual calculation is not on, it is set on Automatic.
I've tried F9 for posterity, with nothing happening.
I've tried CTRL-ALT-SHIFT-F9 also nothing.

The cells Im working on have been imported from an external source, ie
they
are not hand entered but I dont think this is the issue.

They are custom format though, i.e. h:mm:ss.
Once I double-click on the cell and hit return the formula works but
never
before.

PLEASE HELP!!!!Do the formula cells reference the imported data?, have you tried

Trimming the imported data as sometimes hidden characters are imported.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Jacob Skaria]

You data must be in text format....Try the below..

--Copy a blank cell
--Keeping the copy select the range of cells with numeric values
--Right click>PasteSpecial>
--Select 'Add' and click OK.

 

[Gary''s Student]

Your import is at fault. Material has been entered into cells and Excel has
not be given the opportunity to recognize the material as an equation. Try
this example on a new worksheet. In A1 enter:
'=1+2
Because of the apostrophe, Excel treats A1 as Text and not a formula. Copy
A1 and paste/special/value in A2. A2 will display;
=1+2
in both the cell and formula bar. If you now double-click on A2 and touch
ENTER, Excel will recognize the material as a formula and display 3 in the
cell.

You can write a macro to find these "embryonic" formulas and convert them
into real formulas.

 

[raaaabert]

Your import is at fault. Material has been entered into cells and Excel has
not be given the opportunity to recognize the material as an equation. Try
this example on a new worksheet. In A1 enter:
'=1+2
Because of the apostrophe, Excel treats A1 as Text and not a formula. Copy
A1 and paste/special/value in A2. A2 will display;
=1+2
in both the cell and formula bar. If you now double-click on A2 and touch
ENTER, Excel will recognize the material as a formula and display 3 in the
cell.

You can write a macro to find these "embryonic" formulas and convert them
into real formulas.

Wow rapid response team or what. Cool.

Thanks for the suggestions but I'm not that familiar with Macros, perhaps
you could guide me towards one or what to search for...

The problems is they are time entries;
Original (hh:mm:ss.sss), General, Modified (hh:mm:ss)

21:14:52.562 0.885330579 21:14:53
21:14:53.062 0.885336366 21:14:53
21:14:53.562 0.885342153 21:14:54
21:14:54.062 0.88534794 21:14:54
21:14:54.562 0.885353727 21:14:55
21:14:55.062 0.885359514 21:14:55
21:14:55.562 0.885365301 21:14:56
21:14:56.062 0.885371088 21:14:56
21:14:56.562 0.885376875 21:14:57
21:14:57.062 0.885382662 21:14:57
21:14:57.562 0.885388449 21:14:58
21:14:58.062 0.885394236 21:14:58
21:14:58.562 0.885400023 21:14:59

Because the original format is too detailed I have changed it to the
modified format (hh:mm:ss) and I am trying to take out the duplicate of the
times, particularly in seconds. I know that the general format of the number
does not contain identical entries nor does the original but the modified
format does.

So I use the conditional formating formula on the modified time format and
it wont work until I double click-enter in each cell. Yeah I guess I need
that macro....???

 

[raaaabert]

I get this alot, and Ive searched everywhere for the answer.

No macros or hidden sheets active.
Manual calculation is not on, it is set on Automatic.
I've tried F9 for posterity, with nothing happening.
I've tried CTRL-ALT-SHIFT-F9 also nothing.

The cells Im working on have been imported from an external source, ie
they
are not hand entered but I dont think this is the issue.

They are custom format though, i.e. h:mm:ss.
Once I double-click on the cell and hit return the formula works but
never
before.

PLEASE HELP!!!!Do the formula cells reference the imported data?, have you tried

Trimming the imported data as sometimes hidden characters are imported.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158158

Microsoft Office Help

.

No I havent tried trimming.

 

[Gary''s Student]

Here is the macro and instructions:

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


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[raaaabert]

Gary's Student,

I really appreciate the help but the macro did not work.
I did everything to the T, ran the macro in the open workbook
It thought about everything for a while but still no worky
Double click in cells and enter still the only solution.

I will read up on the macro link you sent but dont have time to figure this
out,
do you have an alternative macro?

Again, appreciate your time.

Raaaabert

 

[raaaabert]

Dear GS,

I really appreciate your advice, but unfortunately the macro did not work.
I did everythiing to the T, the macro ran for a few secs but no cigar.
D-click and enter in the cells still the only way to the get the formula to
work.

I will have a good read of the Macro link you sent but i dont have time to
suss out this particular prob - it might take me a few days/weeks.... do you
have any other macros for this prob?

Again much appreciation for you time and knowledge

Raaaabert