Formula not calculating after VBA. Need to click each cell

[Akbarov]

Hello,

I used
screenupdating = false
Application.Calculation = xlCalculationManual

Because code was working very slow. Now it works very fast but not calculating cells. I need to click each cell to calculate result.
Any solution please? I tried replace "=" with "=" but still no luck..

Option Explicit

Sub Sample()
    
    Dim ws As Worksheet
    Dim ColName As String
    Dim ColNumber As Long
    Dim i As Long
    Dim CpyFrom As range
    Dim Cell As range
    Dim ColCounter As Integer
    Dim LR As Long
    
    
    'Turn of screenupdating to improve performance
    'Dim savedScreenUpdating As Boolean
    'savedScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    
    
    'Make Excel calculation manual to improve performance
    'Dim savedCalcMode As XlCalculation
    'savedCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    '~~> Sheet name to format
    Set ws = Sheets("DATA TO FORMAT (2)")
    ColCounter = 1

    'for each rows
    Set CpyFrom = ws.range("L10003:L10542")
    
    ColName = ws.range("B9").Value2
    ColNumber = range(ColName & 1).Column
    
    '~~> Here is the loop from 0 to col number
    For i = 0 To ColNumber - 13
    
     For Each Cell In CpyFrom
        If Cell.Value <> vbNullString Then
            Cell.Offset(0, i + 1).Value = Cell.Offset(0, 1).Formula2R1C1
        End If
    Next Cell
    Next i
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    

    range(Cells(10003, 12), Cells(10542, Rows("10003:10542").Find("*", , xlFormulas, , 2, 2).Column)).Replace "=", "="
    
End Sub

 

[johnnyL]

Try putting the:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

right before 'End Sub'

In other words, move it down past the:

    range(Cells(10003, 12), Cells(10542, Rows("10003:10542").Find("*", , xlFormulas, , 2, 2).Column)).Replace "=", "="

 

[Akbarov]

Thanks for reply. But still no luck.. result is same
Btw I changed this line

Cell.Offset(0, i + 1).Value = Cell.Offset(0, 1).Formula2R1C1

to this also

Cell.Offset(0, i + 1).Formula = Cell.Offset(0, 1).Formula

 

[johnnyL]

Well if you turned stuff off, turned it back on, I am not sure what you are asking for. Can you provide some more info?

 

[Akbarov]

All cells are blank, not showing any result. But when I click in formula and click enter it returns result.
I tried to calculate with VBA
Also clicked "Calculate now"
Still results are blank. It returns results only when I click on formula and press enter.

 

[Akbarov]

One more think I just tested.
When I replace "=" with "-" it works.
Then I tried replace again "-" to "=" it does not works. so I guess problem is in replacement line?
Edit: it doesn't works because it is not formula anymore. So problem is not this.

 

[Alex Blakenburg]

What formula do you have in M10003 ?
Also in N10003 what is the format in the format box, is it General or something else ?

 

[Akbarov]

M is number
N is general
Formula:

=IFERROR(IF(M$10001="AVERAGE",SUMIF(INDIRECT($B10003&"$11:"&$B10003&$C$9),">"&"0",INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1","$11")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1",$C$9)))/IF(ISNUMBER(SEARCH("_DO_",M$1)),"",SUMPRODUCT(--(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1","$11")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1",$C$9))<>""),(INDIRECT($B10003&"$11:"&$B10003&$C$9)))),IF(M$10001="COUNT",COUNTIFS(INDIRECT($B10003&11&":"&$B10003&$C$9),">="&"1",INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","11")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",$C$9)),"<="&"3"),IF(ISNUMBER(SEARCH("_DO_",M$1)),"",SUMPRODUCT(--(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1",11)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(M11),4),"1",$C$9))<>""),(INDIRECT($B10003&"$11:"&$B10003&$C$9)))))),"")

 

[Alex Blakenburg]

Ouch. I would need a copy of your spreadsheet to replicate that.

If I put a simple formula in M10003 your most recent code works fine for me:

Cell.Offset(0, i + 1).Formula = Cell.Offset(0, 1).Formula

In relation to your speed issues you don't need 2 loops.
In theory should also not need that final replace but unless the below accidentally fixes the issue we still need to find a fix for that.
If the issue persists after the below can you just try changing Formula on both sides to Formula2 to see if it makes a difference ?

    '~~> Here is the loop from 0 to col number
    'For i = 0 To ColNumber - 13
        For Each Cell In CpyFrom
            If Cell.Value <> vbNullString Then
                'Cell.Offset(0, i + 1).Formula = Cell.Offset(0, 1).Formula
                Cell.Offset(0, 2).Resize(1, ColNumber - 13).Formula = Cell.Offset(0, 1).Formula
            End If
        Next Cell
    'Next i

 

[Akbarov]

Thank you so much.. I just commented last line ( replacement )
Now it works..