VBA code to find replace formula

[jamesmitchell]

Hi all,

I was wondering if anyone could help.

I have a sheet with hundreds of formulas that seems to make my vba code get stuck as when i remove these formulas it works. My idea was to therefore use replace to find a string and replace it with a '=' at the start of the cell but this does not work.

I have tried ThisWorkbook.Sheets("SheetNamet").Range("H2:T1536").Rows.SpecialCells(xlCellTypeVisible).Replace what:="DELETE", replacement:="=" but this doesnt do anything. When i replace '=' with something else it does work.

Any suggestions?

Thanks

James

 

[Kaper]

Have a look on one of recent threads: https://www.mrexcel.com/board/threads/vba-copy-and-replace-help.1255671/#post-6166005 (this link is to Alex's code, but any of the codes there could be used by you. Of course you shall not search for =INDEX but just for =

 

[mumps]

Try inserting this code at the very beginning of your macro:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

and this code at the very end of your macro:

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

See if this helps.

 

[jamesmitchell]

Try inserting this code at the very beginning of your macro:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

and this code at the very end of your macro:

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

See if this helps.

Thank you, i already have this but for some reason it gets stuck when i have all the formulas in the sheet.

 

[jamesmitchell]

Have a look on one of recent threads: VBA copy and replace help (this link is to Alex's code, but any of the codes there could be used by you. Of course you shall not search for =INDEX but just for =

Thank you for this. I don't really understand it. Is it replacing the cell value with the results of the formula? I cant do this as i cant have the formulas in the cell as it freezes when executing the code.

I would like to either add '=' onto the beginning of a string in a cell to make it into a formula or replace 'Delete' with '=' to make the string into a formula.

Thank you

 

[mumps]

You might want to explore the possibility of eliminating all the formulas and using code to perform those actions.

 

[Kaper]

Well, my idea was indeed to replace formulas with values.
Because as I understood, you want to run some macro which freezes because of presence of many formulas.
So the macro replacing formulas with their values could be run first, then main macro.
What's more - and it would be especially easy if you'd use Alex appraoch from the linked post - you could call your macro just before the end of the replacing macro and immediately after restore previous formulas.

Soemthing like:

Sub temporarilyReplaceFormulasWithValues()
    Dim rng As Range
    Dim arrForm As Variant, arrFormStored As Variant, arrVal As Variant
    Dim i As Long, j As Long

    Set rng = ActiveSheet.UsedRange
    arrForm = rng.Formula
    arrFormStored = arrForm
    arrVal = rng.Value

    For i = 1 To UBound(arrForm)
        For j = 1 To UBound(arrForm, 2)
            If InStr(1, arrForm(i, j), "=", vbTextCompare) Then
                arrForm(i, j) = arrVal(i, j)
            End If
        Next j
    Next i
    rng.Formula = arrForm

' here call your macro which has problems with formulas like:
Call MyOriginalMacroFreezingWorkbookBecauseOfManyFormulas

'and finally restore formulas
   rng.Formula = arrFormStored

End Sub

 

[jamesmitchell]

Thank you for your help. I appreciate it.

Sorry, the code that freezes copies data from multiple sheets and appends it into a new sheet. The formulas look at these values that are copied into this sheet. So the formula values are not calculated until the code completes and copies the values over.

I have tried copying the values to a different sheet and then copy them into the lookup sheet but this still freezes!

I will provide the full code when I am back at work. Maybe there is a problem with the code that copies the values over into the master sheet.