Use Find and Replace in a macro

**bigfishprf** · 02-19-2013, 01:01 PM

I have 2 cells on a worksheet.

One contains text I want to search for and the other contains the text I want to replace it with.

I have a macro which does it all but I need the macro to access the above 2 cells each time as they will always be different. Is there a simple VB command for this please?

**Cheeky Charlie** · 02-19-2013, 01:11 PM

sheets("sheet 1").range("A1:B2").replace sheets("sheet 2").range("C1"), sheets("sheet 2").range("D1"), xlvalues

sheet 1 is the sheet with the cells you want to perform f&r and A1:B2 is the address of the cells you want to run your find & replace over (use .cells.replace if you want it across all)
sheet 2 is the sheet with the find and replace strings, C1 is the address for the find string, D1 is the address for the replace string

**bigfishprf** · 02-19-2013, 04:51 PM

Many thanks. I'll give that a try in the morning and if I can get it to work, donation to Actioaid will follow!

**bigfishprf** · 02-21-2013, 03:55 AM

Hi Cheeky Charlie. I inserted the code you suggested and I am getting an Error '9' Subscript out of range. Any ideas on what I have done wrong? Copy of Macro is shown below. Hope you can help. Many thanks
Sheets("Monthly Summary").Select
ActiveSheet.Unprotect
Sheets("New Store Skeleton").Select
ActiveSheet.Unprotect
Sheets("Monthly Summary").Select
Rows("4:32").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("New Store Skeleton").Select
Range("A4:AQ32").Select
Selection.Copy
Sheets("Monthly Summary").Select
Range("A4").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-3
Sheets("Monthly Summary").Select
Sheets("Monthly Summary").Range("A4:AZ32").Replace Sheets("Macros").Range("B6"), Sheets("Macros").Range("B9"), xlValues
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("New Store Skeleton").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Monthly Summary").Select

**Cheeky Charlie** · 02-21-2013, 05:35 AM

Hi there, please put your code in tags, then I'll gladly help

**bigfishprf** · 02-21-2013, 06:08 AM

Hi again. Hope I've done this properly! And thanks again in advance.

Sheets("Monthly Summary").Select
ActiveSheet.Unprotect
Sheets("New Store Skeleton").Select
ActiveSheet.Unprotect
Sheets("Monthly Summary").Select
Rows("4:32").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("New Store Skeleton").Select
Range("A4:AQ32").Select
Selection.Copy
Sheets("Monthly Summary").Select
Range("A4").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-3
Sheets("Monthly Summary").Select
Sheets("Monthly Summary").Range("A4:AZ32").Replace Sheets("Macros").Range("B6"), Sheets("Macros").Range("B9"), xlValues
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("New Store Skeleton").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Monthly Summary").Select

**Cheeky Charlie** · 02-21-2013, 06:27 AM

My mistake, I used the wrong argument (xlValues)

I think the below does what you need, but would need an example from you to test it:

    With Sheets("Monthly Summary")
        .Unprotect
        .Rows("4:32").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("New Store Skeleton").Range("A4:AQ32").Copy .Range("A4")
        .Range("A4:AZ32").Replace Sheets("Macros").Range("B6"), Sheets("Macros").Range("B9"), xlPart
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With

Please do look at the code, and see how the changes I've made work. There are two main things I've done to improve the readability and efficiency of the code - replace "thing.select ... selection.action" with "thing.action" and use of the 'with' construct.

If this doesn't do what you need, upload an example with your next post.

**bigfishprf** · 02-21-2013, 10:37 AM

Great Results thanks - Everything works perfectly now.

If its OK with you, I will make donation to MacMillan Cancer Support as I regularly raise funds to help them.

And thanks again for your help

**Cheeky Charlie** · 02-21-2013, 12:52 PM

Absolutely, I think MacMillan is a very worthy cause, I sincerely hope I never need their services.

I don't think you would donate if you didn't want to anyway, but I feel I ought to say: The help I provide here is completely free - I wouldn't want to imply that there is any contract (social, implied or otherwise!)