Lost all formulas in cells

[Grizlore]

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

 

[Yard]

Is there some VBA code in your workbook?

 

[Grizlore]

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]

The code doesn't do anything BUT put values in cells, so on that basis I would just delete the lot!

 

[Grizlore]

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

 

[Yard]

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]

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]

Maybe

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]

Thanks, however your code didnt work, it copying the data and put lots of #N/A's everywhere

 

[Yard]

oops, try

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