Need macro for mass control find replace

[tonyjyoo]

Hello,

I have a workbook I update every month, and currently I have just been using control find replace to update. However, this takes me 10+ minutes for each of 5 tabs, as there are thousands of cells it needs to replace that is linking to a previous month's file.

Is there a way to quickly do this? I've tried everything I know.

Thanks,
Tony

 

[tonyjyoo]

Hello,

I have a workbook I update every month, and currently I have just been using control find replace to update. However, this takes me 10+ minutes for each of 5 tabs, as there are thousands of cells it needs to replace that is linking to a previous month's file.

Is there a way to quickly do this? I've tried everything I know.

Thanks,
Tony

bump

 

[tonyjyoo]

bump

bump

 

[Atroxell]

Hello,

I have a workbook I update every month, and currently I have just been using control find replace to update. However, this takes me 10+ minutes for each of 5 tabs, as there are thousands of cells it needs to replace that is linking to a previous month's file.

Is there a way to quickly do this? I've tried everything I know.

Thanks,
Tony

Have you tried recording a macro, and then building a loop to loop through the worksheets?

 

[shg]

Data > Edit Links

 

[tonyjyoo]

Data > Edit Links

tried this and it does not speed it up. ALso tried copy find replace macro but that didnt help either.

 

[tonyjyoo]

tried this and it does not speed it up. ALso tried copy find replace macro but that didnt help either.

anyone?

 

[jb2_86_uk]

Your requirement is far too broad to expect a resolution straight off the bat.

Why did recording a macro not help?

Can you provide the vba code your recorded macro produced? Either I or other members of this forum will be able to help if we have an idea what you are trying to achieve, such as target & replacement values, ranges, sheets etc.

Cheers
John

 

[tonyjyoo]

Your requirement is far too broad to expect a resolution straight off the bat.

Why did recording a macro not help?

Can you provide the vba code your recorded macro produced? Either I or other members of this forum will be able to help if we have an idea what you are trying to achieve, such as target & replacement values, ranges, sheets etc.

Cheers
John

The below code is what I have tried, but still has no success, takes minutes to complete.

Sub Macro1()
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    ' fill your range in here
    Range("A1:AN350").Select
    ' choose what to search for and what to replace with here
    Selection.Replace What:="03 NPH Cash Position 17", Replacement:="02 NPH Cash Position 17", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull
 
End Sub

Your feedback is greatly appreciated.

 

[jb2_86_uk]

Hi Tony,

This forum is awash with far more competent and knowledgeable folk than me who may have alternative methods, but that is how I would have gone about it too.

I just mocked up a sheet of the same sized range, the actual find & replace only took around 5-10 secs on my pretty meagre work PC. The bottleneck is the subsequent recalculating of up-to 70,000 formulae (350 [rows] * 40 [columns] * 5 [sheets]), exacerbated greatly if the formula are referencing external data sources.

I'd love to be proved wrong, but I think the only way you'll speed it up is by improving the efficiency of your sheets. What data are they pulling from the other document/sheets? Could the same result be achieved by connecting the other document(s) as a data source and then referencing them locally?