Weird formula Problem

[simeybt]

All,
I have seen this problem before but I cant remember how I fixed it. The problem is as follows. I simply make a cell on one sheet = to the contents of another cell on an other sheet. But instead of showing me the Value it shows me formula. Both cells are set up with the same formatting and the workbook is set up to NOT show formulas. The fix I have for it is to make a cell further down the sheet = to the cell on the other sheet I want. Then copy this to the correct location.

Any Idea's

Simon

 

[Chance1234]

tools/ options/ view

untick show formulas

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[xlbo]

And post in the MSOffice forum next time please - this has nothing to do with VBA

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[macropod]

Hi Simon,

The problem you're having is caused by the cell into which the formula was being entered being formatted as 'text'. Chnage the cell formatting to general, or something else other than 'text' and it should display the results instead of the formula.

Cheers

 

[xlbo]

Sorry macropod - that won't work.
You must make excel re-evaluate the cells after changing the format. A sub like this would do the trick:

Sub revert()
For Each c In Selection
    c.Formula = c.Formula
Next
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[macropod]

Hi xlbo,

The solution I posted works if you change the formatting before entering the formula ... but not if you change it afterwards.


Cheers

 

[macropod]

PS: It also works afterwards if you select the formula in the errant cell and press the enter key.

Cheers

 

[xlbo]

yup but the user already has the cells formatted as text with the formulas showing (if this is the case and it isn't a "Show Forumulas" issue)

Also, going through a list of hundreds or thousands of formulae and pressing enter isn't really workable - that's why I showed the code - which basically does just that

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[simeybt]

Thanks Mcaropod and xlbo. I used the both of your posts to solve my problem.


Simon

P.S. I’ll post things like this in the MSOffice Forum(didn’t realise it existed). just force of habit as this is the forum I use the most.