+ Reply to Thread
Results 1 to 13 of 13

VBA to find and replace arrays in specific columns

  1. #1
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    VBA to find and replace arrays in specific columns

    Hello,

    I have two arrays. One is the "Find" array, the other is the "replace" array. They are replacing values in specific columns on another sheet in the same workbook. Both these arrays are driven by formulas. I have a super long program written that goes through and replaces each value in each specific column, but it keeps failing. Can anyone help with the code for this? I have a hunch my original code would be useless, so I haven't included it. I've posted the table with the find and replace arrays, and the specific columns they will be finding and replacing on in the other sheet.

    find_replace.JPG

    Thank you in advance.
    Last edited by ahsteinb; 07-16-2019 at 10:55 AM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    I assume your find/replace list is not on the same sheet as the one where the values are replaced?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA to find and replace arrays in specific columns

    Correct, they are on two separate sheets

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    Give this a shot:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 07-16-2019 at 11:30 AM. Reason: Added screen updating lines.

  5. #5
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA to find and replace arrays in specific columns

    It appears to work perfectly. Thank you so much!

    Last question, if I wanted it to loop through multiple sheets in the same workbook, would I just need to add a "For each worksheet in workbook" function, or would it be something else?

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    How exactly you implement that will depend on whether it is all sheets (except the one with find/replace list) or just a few. But yes, in essence it would be a for ... each loop. If you give me the details I can add that to the code, unless you are up to the task yourself of course

  7. #7
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA to find and replace arrays in specific columns

    Let me give it a shot myself and I'll reply if I can't get it. Only way to learn right?

    Thank you again!

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    My pleasure

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    If you need help don't hesitate to ask. If you solve it on your own, please come back and mark the thread as solved so we know no follow-up is required. Thanks!

  10. #10
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA to find and replace arrays in specific columns

    So this was my solution, doesn't seem to be working. Any ideas?

    Dim wsr As Worksheet
    Dim wss As Worksheet
    Dim wsSheet As Worksheet
    Dim i As Long, r As Long


    Set wss = ThisWorkbook.Worksheets("Weeks")
    Set wsr = ThisWorkbook.Worksheets("Maria & Enzo's Working Tab")
    Set wsi = ThisWorkbook.Worksheets("wksInput")
    lr = wss.Range("F" & Rows.Count).End(xlUp).Row
    For Each wsSheet In Worksheets
    If wsSheet.Name <> "wksInput" Or wsSheet.Name <> "Weeks" Then
    For i = 2 To lr
    wsr.Columns(wss.Range("F" & i).Value).Replace what:=wss.Range("G" & i).Value, replacement:=wss.Range("H" & i).Value, lookat:=xlPart
    Next i
    End If
    Next wsSheet

  11. #11
    Registered User
    Join Date
    06-24-2019
    Location
    Buffalo, NY
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA to find and replace arrays in specific columns

    I just need the code to loop through every sheet but I know my solution is wrong. If I got the code to loop, I could modify it to my needs. Thanks again.

  12. #12
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    Please use code tags when posting code (highlight what is code, and click the # icon)

    Do you want to loop through ALL sheets in the workbook? Or you want to just do the replace in 2 sheets out of many? "For Each wsSheet In Worksheets" loops through them all.

    Just to confirm... you have 2 sheets that contain "find and replace" lists? Or you want to replace values in 2 sheets based on just one list? (ideally tell me what each of the 3 names is in terms of "to replace" or "list")

    If you have 2 sheets with find and replace values, are their columns set up the same?
    Last edited by Arkadi; 07-16-2019 at 01:17 PM.

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: VBA to find and replace arrays in specific columns

    I haven't posted loop code just because how to structure it depends a bit on my questions above. Let me know, and I'll post updated code

+ 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 find and replace in a specific column specific words to an abreviation
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2018, 06:05 AM
  2. [SOLVED] Open text file and store specific columns using arrays
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-23-2017, 02:45 PM
  3. [SOLVED] Find and replace using arrays
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2017, 08:23 AM
  4. [SOLVED] Find a specific sum for two different arrays..(more explanation in the post)
    By bach1229 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-03-2015, 08:21 AM
  5. [SOLVED] formulas to automatically find and replace data from specific columns
    By syncguy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-06-2015, 06:47 PM
  6. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  7. Find and Replace Arrays
    By s2nguyen in forum Excel General
    Replies: 5
    Last Post: 10-29-2008, 10:57 AM

Tags for this Thread

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