Formula's disappear

[VBA]

I'm all confused

I run a macro, and suddenly the formula's in my sheet disappear - leaving only the values they produce

Thus: when I run the macro, {=sum(A1:A100)/counta(A1:A100)} becomes {100}, which is indeed the value of that formula but why can't I see the formula behind it anymore when I click on the cell?

used macro

Greetings,

 

[Andrew Poulsom]

The code you posted is overwriting your formulas with the values they return:

.Value = .Value

 

[VBA]

Ok, thank you for the swift answer

I'll try skipping the cells with formula's then Should be possible.

Greetings.

 

[Andrew Poulsom]

You can use:

If Not c.HasFormula Then

 

[VBA]

Thanks, it's working nicely! Really appreciated

 

[VBA]

For some reason it does not work anymore
It gives the error: "next without for", so I guess that 2nd If destroys it all, but I said in an earlier post that it worked nicely! I do not know why I did then and not now.

Anybody an idea how to change this into something which works? I need the code in red as otherwise I loose the formula's in the sheet when I delete the blank spaces.

For more info, see first macro and addition. I guess I should place it somewhere else?

Sub NoBL()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Left(c.Value, 1) = Chr(160) Then c.Value = Right(c.Value, Len(c.Value) - 1)
If Not c.HasFormula Then
c.Value = c.Value
Next c
End Sub

 

[Andrew Poulsom]

You are missing End If before Next c.

 

[VBA]

Thank you

It did not occur to me that End If was needed, as the macro worked perfectly without it as long as I had only one if-statement in it. I am still not quite sure how this works, but when I find the time I will look into it.

Thanks again for your swift reply!

 

[Andrew Poulsom]

You don't need End If if the statement is all on one line like this:

If Not c.HasFormula Then c.Value = c.Value