Find and replace macro for multiple worksheets/items - newbie
Results 1 to 8 of 8

Thread: Find and replace macro for multiple worksheets/items - newbie
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find and replace macro for multiple worksheets/items - newbie

    Hi all,

    This has been quite a wonderful forum and great place to learn.

    I have the below problem which I require some advice.

    1. I am trying to consolidate the sales of a few items from 3 different sources (3 different worksheets)
    2. These 3 different worksheets name all these items differently.
    3. What I did was I wrote a simple macro to find and replace all these different variations into one consistent naming
    4. The issue is I used arrays in my macro and there are way too many items to cater for
    5. Like to ask if there is an easier way to do it if I continue with the find and replace method?
    6. Or if there is even an easier solution to tackle this (without using find and replace)?

    Below are my working files and VBA text. Thank you sirs in advance

    Working files

    Working file A
    Fruit Sales
    Apple1 10
    Orangie 4
    Pineapple 21
    Apple1 4
    Apple1 2
    Applie 3
    Pineapplie 4
    Orange 5
    Mango 2

    Working File B
    Fruit Sales
    applie 10
    Orange2 4
    Pineapp 21
    Apple1 4
    Apple2 2
    Mago 3
    Orangie 4
    Mango 5
    Mannngo 2


    Working File3
    Fruit Sales
    Pineap 10
    Orange2 24
    Pineapp 18
    Apple3 42
    Apple4 56
    Mangooo 24
    Orangie 55
    Mango 14
    Mannngo 27


    Desired output
    all combined into one file
    Fruit Sales
    Apple XX
    Mango XX
    Orange XX
    Pineapple XX


    Macro text
    Sub Multi_FindReplace()
    'PURPOSE: Find & Replace a list of text/values throughout entire workbook


    Dim sht As Worksheet
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long

    fndList = Array("Apple1", "apple2", "mago", "Orangie")
    rplcList = Array("Apple", "Apple", "Mango", "Orange")

    'Loop through each item in Array lists
    For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next sht

    Next x

    End Sub

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Put the relationship on a sheet called "Custom" as shown in the following example:

    Custom

     AB
    1fndListrplcList
    2Apple1Apple
    3Apple2Apple
    4Apple3Apple
    5magoMango
    6OrangieOrange


    Try this:

    Code:
    Sub Multi_FindReplace()
        'PURPOSE: Find & Replace a list of text/values throughout entire workbook
        Dim sht As Worksheet, sh As Worksheet, c As Range
        Set sh = Worksheets("Custom")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))  'Loop each item in column "A"
            For Each sht In ActiveWorkbook.Worksheets                       'Loop each sheet in Workbook
                If sht.Name <> sh.Name Then
                    sht.Cells.Replace c.Value, c.Offset(, 1).Value, xlPart
                End If
            Next
        Next
        MsgBox "Done"
    End Sub
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Awesome. Thanks Dante.

    However, Can I check with you on how do I make the desired values combine and appear on a new worksheet with the correct output?

    Or should I combine them first, then find and replace? Thanks

    Regards


    Quote Originally Posted by DanteAmor View Post
    Put the relationship on a sheet called "Custom" as shown in the following example:

    Custom

    A B
    1 fndList rplcList
    2 Apple1 Apple
    3 Apple2 Apple
    4 Apple3 Apple
    5 mago Mango
    6 Orangie Orange


    Try this:

    Code:
    Sub Multi_FindReplace()
        'PURPOSE: Find & Replace a list of text/values throughout entire workbook
        Dim sht As Worksheet, sh As Worksheet, c As Range
        Set sh = Worksheets("Custom")
        For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))  'Loop each item in column "A"
            For Each sht In ActiveWorkbook.Worksheets                       'Loop each sheet in Workbook
                If sht.Name <> sh.Name Then
                    sht.Cells.Replace c.Value, c.Offset(, 1).Value, xlPart
                End If
            Next
        Next
        MsgBox "Done"
    End Sub

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Quote Originally Posted by CCK0857 View Post
    Awesome. Thanks Dante.

    However, Can I check with you on how do I make the desired values combine and appear on a new worksheet with the correct output?

    Or should I combine them first, then find and replace? Thanks

    Regards
    You can explain it with examples.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Thanks Dante and apologies for not providing sufficient info earlier.

    So lets say I have three working files. I now know how to replace the wrong naming with the correct naming thanks to your guidance.

    So if I want to go one step further. After replacing the wrong naming with the correct names, I want to auto sum the sales and put them into one new excel sheet. How do i do so? Examples below

    Many thanks once again


    Working file A
    Fruit Sales
    Apple1 10
    Orangie 4
    Pineapple 21
    Apple1 4
    Apple1 2
    Applie 3
    Pineapplie 4
    Orange 5
    Mango 2


    Working File B
    Fruit Sales
    applie 10
    Orange2 4
    Pineapp 21
    Apple1 4
    Apple2 2
    Mago 3
    Orangie 4
    Mango 5
    Mannngo 2



    Working File3
    Fruit Sales
    Pineap 10
    Orange2 24
    Pineapp 18
    Apple3 42
    Apple4 56
    Mangooo 24
    Orangie 55
    Mango 14
    Mannngo 27



    Output excel sheet (combined sales)

    Fruit Sales
    Apple XX
    Mango XX
    Orange XX
    Pineapple XX




    Regards


    Quote Originally Posted by DanteAmor View Post
    You can explain it with examples.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Still missing information.
    Column of fruits, column of sales.
    Assuming that they are columns A and B and data begin in row 2

    Try this

    Code:
    Sub one_step_further()
        Dim sh As Worksheet, sh1 As Worksheet, c As Range, f As Range
        Set sh1 = Sheets("combined")
        sh1.Rows("2:" & Rows.Count).ClearContents
        For Each sh In Sheets
            Select Case sh.Name
                Case "Custom", sh1.Name
                Case Else
                    For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
                        Set f = sh1.Range("A:A").Find(c, LookIn:=xlValues, lookat:=xlWhole)
                        If Not f Is Nothing Then
                            f.Offset(, 1) = f.Offset(, 1) + c.Offset(, 1)
                        Else
                            sh1.Range("A" & Rows.Count).End(xlUp)(2).Value = c.Value
                            sh1.Range("B" & Rows.Count).End(xlUp)(2).Value = c.Offset(, 1)
                        End If
                    Next
            End Select
        Next
    End Sub
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Thanks. assumptions are correct. Let me give it a quick try

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find and replace macro for multiple worksheets/items - newbie

    Quote Originally Posted by CCK0857 View Post
    Thanks. assumptions are correct. Let me give it a quick try
    Let me know any questions and I will gladly review it.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •