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..
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..
VBA Code:
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