+ Reply to Thread
Results 1 to 11 of 11

Using Table with Find & Replace Macro

  1. #1
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Using Table with Find & Replace Macro

    Hi folks!

    I know this should be easy, but I just can't seem to get it to work.

    My workbook is attached...here's what I need to do:

    I want to have a table on the "ClientList" tab, 2 columns...column 1 is "Find" value, Coumn 2 is "Replace" value

    I want to run this macro on the data in the "PrepSheet" tab so that the names in the "Account Manager" & "Customer" columns are changed appropriately.

    I need the table because I have a lot of "Customer" replacements to make and arrays just aren't working for me.

    Any help/guidance you can give me would be great! I've worked with macros a little bit, but I am far from being proficient.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Using Table with Find & Replace Macro

    Please Login or Register  to view this content.
    I attached the workbook including the macro
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Re: Using Table with Find & Replace Macro

    Thank you so very much! i really appreciate the assistance! Have a wonderful day!

  4. #4
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Re: Using Table with Find & Replace Macro

    Do I need to go into the macro and change anything else other than the "PrepSheet" setting at the end? I just ran it and it didn't do anything....

  5. #5
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Using Table with Find & Replace Macro

    you cannot see anything because the update (overwriting the data with identical data) is not visible

    to see it working you should either modify your search and replace list, your original data or simple erase the content of PrepSheet_2
    or change name in the macro from PrepSheet_2 to PrepSheet to see the result on your original data

  6. #6
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Re: Using Table with Find & Replace Macro

    OK, so I added a couple things to table 1, erased the content of Prep Sheet_2, changed the name in the macro to "Prep"Sheet"...and all it did was change the names in the "Account Manager" column. I need it to be able to also change values in the "Customer" column reading from that same table...am I still doing something wrong? I haven't edited anything else in the macro....

  7. #7
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Using Table with Find & Replace Macro

    please attach your file and mark the cells with a background color where you expect a change

  8. #8
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Re: Using Table with Find & Replace Macro

    OK, file is attached...On the "PrepSheet" tab, the yellow highlight are the columns that need changed by the info in Table 1...the blue highlight shows an example to the right of how it needs to look once the macro has been run. Thanks so much for taking the time to help me with this...its been a frustrating day
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Using Table with Find & Replace Macro

    I used PrepSheet_2 as test output so that the original data is not modified. you should do the same.

    why it is not working as expected:
    1) you changed the code of the macro (not only the name). you commented out
    Please Login or Register  to view this content.
    . But I added the comment that this can be only used if the whole cell content is the same as the find string. But
    "AETNAEAP - Aetna - Employee Assistance Program" <> "Aetna - Employee Assistance Program"
    ==> you have to use the replace function!
    2) in Table1 "Aetna - Medical Individual" is not found in "AETNAMI - Aetna - Medicare Individual"
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-25-2020
    Location
    Exton, PA USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    11

    Re: Using Table with Find & Replace Macro

    Hello there! This is great! yes, i see where the text in Table1 didn't match...that's me going too fast Otherwise, everything looks good with your latest instructions! Thank you so much for your time! Its really been helpful and educational! have a great day!

  11. #11
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Using Table with Find & Replace Macro

    welcome !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to Find/Replace Record in One Table Based on Records in Another Table
    By DavidFC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2018, 04:41 AM
  2. Find-replace from static table with 2,630 find items
    By OhioRuss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2018, 12:44 AM
  3. Problems exporting a table - find/replace data inside a table in word through excel vba
    By elvagonumero1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2018, 04:33 PM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. Find/Replace macro with lookup table of change from to change to
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2013, 07:37 AM
  6. (macro) Find and Replace Using Reference Table
    By jamesdella in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2012, 03:03 PM
  7. Find/Replace Macro Using Replace Table
    By LampCommandr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-24-2011, 11:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1