Speed Up Replace All through Macro

[Peteor]

Hello again! I have the following disabled:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

and my macro is doing the following to the active sheet:

Cells.Replace What:=Existing_Workbook_Name, Replacement:=New_Workbook_Name, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Is there any way to accomplish this faster? It is far too slow currently. Also, the replacements are all part of VLOOKUP() formula present within Rows 1 - 450, and Columns N:W of the worksheet.

 

[Fluff]

Try

With ActiveSheet.UsedRange.SpecialCells(xlFormulas)
   .Replace Existing_Workbook_Name, New_Workbook_Name, _
      xlPart, , False, , False, False
End With

 

[Peteor]

It still takes quite a bit of time. The string value for Existing_Workbook_Name is present in rows 1 - 450, Columns N:W, and I would like to replace with the string value of Existing_Workbook_Name.

is there another way maybe, or is there another setting you would recommend adjusting?

 

[Fluff]

How about

With ActiveSheet.Range("N1:W450")
   .Replace Existing_Workbook_Name, New_Workbook_Name, _
      xlPart, , False, , False, False
End With

 

[Peteor]

It still takes quite a while. I believe it may be the the replace part of the operation, and that it is editing formulas rather than string values. Is there another way? Do you think it would be faster if I was to clear the cell contents of these columns, and replace them with new VLOOKUP()s with the variable "New_Workbook_Name"s string? If you agree this may be faster, I will test, and provide Feedback to this thread.

 

[Fluff]

It might be faster, the only way to find out would be to try.

 

[Peteor]

I decided not to use the replace operation, and instead replaced the entire cell contents. Using VBA, replacing strings within formulas turned out to be FAR too slow. The code below operated at near instant speed for my purposes:

Range("N2:W450").ClearContents

Range("N2").Select
ActiveCell.Formula = "=VLOOKUP(C2, Filepath[" & New_Workbook_Name & "]Filepath,11,FALSE)
Range("O2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,12,FALSE)"
Range("P2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,13,FALSE)"
Range("Q2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,14,FALSE)"
Range("R2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,15,FALSE)"
Range("S2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,16,FALSE)"
Range("T2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,17,FALSE)"
Range("U2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,20,FALSE)"
Range("V2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,21,FALSE)"
Range("W2").Select
ActiveCell.Formula = "=VLOOKUP(C2,Filepath[" & New_Workbook_Name & "]Filepath,22,FALSE)"
Range("N2:W2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("N2:W450"), Type:=xlFillDefault
Range("N2:W450").Select

As always, thank you Fluff for your input. It is what helped me in the right direction for this one.

 

[Fluff]

Glad you sorted it & thanks for the feedback