VBA Find and Replace Faster

[hrosenb1016]

I am trying to do a giant find and replace in VBA. It takes hours. I have to find and replace 2 areas in each formula of each cell. The parts I am changing link to other sheets. When I change the first part (Oct to Nov) but not the second (10-31 to 11-30) I get a popup notification which I turned off. All of the cells don't link to the same sheet otherwise that would probably be easier. Any suggestions?? Please!

Sub FindandReplace()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Range("A:C").Replace What:="Oct", Replacement:="Nov", _
LookAt:=xlPart, MatchCase:=False
Range("A:C").Replace What:="10-31", Replacement:="11-30", _
LookAt:=xlPart, MatchCase:=False

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull
Application.DisplayAlerts = True

End Sub

 

[Scott Huish]

Why don't you either have the formulas generate the value you need or just put those values in 2 cells then have the formula refer to those cells, then you won't have to do this all the time.

 

[Rick Rothstein]

I am trying to do a giant find and replace in VBA. It takes hours. I have to find and replace 2 areas in each formula of each cell. The parts I am changing link to other sheets. When I change the first part (Oct to Nov) but not the second (10-31 to 11-30) I get a popup notification which I turned off. All of the cells don't link to the same sheet otherwise that would probably be easier. Any suggestions?? Please!

Sub FindandReplace()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Range("A:C").Replace What:="Oct", Replacement:="Nov", _
LookAt:=xlPart, MatchCase:=False
Range("A:C").Replace What:="10-31", Replacement:="11-30", _
LookAt:=xlPart, MatchCase:=False

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull
Application.DisplayAlerts = True

End Sub

I see you turn EnableEvents on at the end of your code, but I don't see where you turned them off at. If you add the line of code to turn events off at the beginning of your code, does it speed things up for you?

EDIT NOTE: Just saw Scott's suggestion. While what I posted was an attempt to answer the question you asked, Scott's suggestion goes to the heart of the matter and should solve all your problems once implemented.

 

[hrosenb1016]

I appreciate the suggestion Scott and that is definitely a solution for the long term but right now I need something that will work now.

Rick - I turned off the EnableEvents and if its faster its not noticeable. Any other suggestions?

Thanks for your help guys!!