Formula shown as text in cell

[Lisarella117]

Hello,

I already used the forum search but nothing could help me.

I have a code which first inserts a new column and then inserts a formula into the first 300 cells of this new row.

Unfortunately the Formula is shown as text, and does not function.
I know that by activating each cell by pressing F2 it works, but as I have to do it for 300 cells in each of 5 sheets, I would appreciate if the macro does it automatically.

Here is the code:

Sub SAPReportFormatting()

Dim Cell As Range

Range("C1").Select
Selection.EntireColumn.Insert

Range("C1:C300").Select
For Each Cell In Selection
If Cell.Value = "" Then Cells.FormulaLocal = "=Trim(Left(D " & Cell.Row & " ,11))"
Next Cell

Range("C1:C300").Select
For Each Cell In Selection
If Cell.Value = "=Trim(Left(D" & Cell.Row & ",11))" Then Cell.NumberFormat = "General"

Next Cell

End Sub

Right now it does not function anymore and I don't know why, because this code a few minutes ago worked...

Thanks for helping me! My VBA book has no solution ..

 

[repairman615]

Hello,

Try replacing:

Then Cells.FormulaLocal =
with
Then Cell.Formula =

-Jeff

 

[Sektor]

SAP creates awful Excel files. I know it.

[COLOR="Blue"]Sub[/COLOR] SAPReportFormatting()

    [COLOR="Blue"]Dim[/COLOR] Cell [COLOR="Blue"]As[/COLOR] Range

    Range("C1").EntireColumn.Insert

    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] Cell [COLOR="Blue"]In[/COLOR] Range("C1:C300")
        [COLOR="Blue"]With[/COLOR] Cell
            [COLOR="Blue"]If[/COLOR] IsEmpty(.Value) [COLOR="Blue"]Then[/COLOR]
                .NumberFormat = "General"
                .Formula = "=Trim(Left(D " & .Row & " ,11))"
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[VoG]

Try

Sub SAPReportFormatting()
Columns("C").Insert
With Range("C1:C300")
    .NumberFormat = "General"
    .Formula = "=TRIM(LEFT(D1,11)"
End With
End Sub

 

[repairman615]

Maybe:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SAPReportFormatting()<br><br><SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br><br>    Range("C1").EntireColumn.Insert<br>        <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Range("C1:C300")<br>        <SPAN style="color:#00007F">If</SPAN> Cell.Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            Cells.FormulaLocal = "=Trim(Left(D " & Cell.Row & " ,11))"<br>            Cell.NumberFormat = "General"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Cell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>