Lost all formulas in cells

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Help please

This keeps happening, and I can't see why...

On some worksheets and spreadsheets, when I save the formulas disappear, only to be replaced by the previously calculated values. It has become a real pain.

I assume this is something simple.

Can anyone help please?

Thanks & Regards
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
No, I checked that.... however... following your question...

I have just thought that this code MAY have been run from a 'personal.xls'. I shall have to ask a colleague

_______________
Sub TrimEVERYTHING()

Set rng = ActiveSheet.UsedRange

rng.Value = Evaluate("IF(ROW(" & rng.Address & "),IF(COLUMN(" & rng.Address & "),TRIM(" & rng.Address & ")))")

MsgBox "TRIMMED ;)"

End Sub
_______________

I presume that the above would remove formulas, can it be amended to stop that ?


Thanks for your reply
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
The code doesn't do anything BUT put values in cells, so on that basis I would just delete the lot!
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259

ADVERTISEMENT

The code "trims" the data pulled from a Mainframe, apparently. (aka removing all the unwanted spaces etc)
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Yes, but your question was how to stop these values being entered, no?

Or do you mean that the code overwrites EVERY cell in the sheet, and you want to preserve those cells where you have put your own formulas?
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259

ADVERTISEMENT

Thanks...

Yes, my question was initially "why are my formulas being lost" however following your reply... I found out that a colleague runs that "Trimming" code.. which seems to be the cause

So now, i am wondering whether the "trimming" code could be amended to leave the formulas as they are, and only TRIM cells which have no formula.

Thanks again
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Maybe
Code:
Sub TrimNonFormulas()
Dim rng, rngC, rngUnion As Range
 
Set rng = ActiveSheet.UsedRange
Set rngUnion = Nothing
 
For Each rngC In rng
    If Left(rngC.Formula, 1) <> "=" Then
        If rngUnion Is Nothing Then
            Set rngUnion = rngC
        Else
            Set rngUnion = Union(rngUnion, rngC)
        End If
    End If
Next rngC
 
rngUnion.Value = Evaluate("IF(ROW(" & rng.Address & "),IF(COLUMN(" & rng.Address & "),TRIM(" & rng.Address & ")))")
 
MsgBox "TRIMMED"
 
End Sub
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Thanks, however your code didnt work, it copying the data and put lots of #N/A's everywhere
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
oops, try
Code:
Sub TrimNonFormulas()
 
Dim rng, rngC, rngUnion As Range
 
Set rng = ActiveSheet.UsedRange
Set rngUnion = Nothing
 
For Each rngC In rng
    If Left(rngC.Formula, 1) <> "=" Then
        If rngUnion Is Nothing Then
            Set rngUnion = rngC
        Else
            Set rngUnion = Union(rngUnion, rngC)
        End If
    End If
Next rngC
 
For Each rngC In rngUnion
    rngC.Value = Evaluate("IF(ROW(" & rngC.Address & "),IF(COLUMN(" & rngC.Address & "),TRIM(" & rngC.Address & ")))")
Next rngC
 
MsgBox "TRIMMED"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top