+ Reply to Thread
Results 1 to 3 of 3

VBA Find & Replace Multiple Values

Hybrid View

hobbiton73 VBA Find & Replace Multiple... 10-18-2013, 02:32 AM
NU2vba Re: VBA Find & Replace... 10-18-2013, 03:28 AM
hobbiton73 Re: VBA Find & Replace... 10-18-2013, 03:54 AM
  1. #1
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    VBA Find & Replace Multiple Values

    Hi, I wonder whether someone may be able to help me please.

    I've put together a very short script (below) which finds and replaces specific text values.

     
    If ws.Name = "Monthly Projects" Then
                    With ws.Range("C5:C" & LastRow)
                        .Replace What:="/IMS", Replacement:="IMS", LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                        ReplaceFormat:=False
                    End With
    End If
    The code works fine, but I'm now looking to expand this and include additional text values with wildcards to find and replace e.g. replace "*Business*" with "BT" and "*Personal*" with "PT", but I'm a little unsure about how to do this.

    I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to achieve this.

    Many thanks and kind regards

  2. #2
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Re: VBA Find & Replace Multiple Values

    Hi hobbiton73,

    Here's a snippet of code from macro I recorded using Find & Replace Function that you can modify to suit your needs.


        With Cells
             .Replace What:="*BUSINESS*", Replacement:="BT", LookAt:=xlPart, _
              SearchOrder:=xlByRows, MatchCase:=False
             .Replace What:="*PERSONNEL*", Replacement:="PT", LookAt:=xlPart, _
              SearchOrder:=xlByRows, MatchCase:=False
        End With
    HTH =)

  3. #3
    Forum Contributor
    Join Date
    05-26-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    682

    Re: VBA Find & Replace Multiple Values

    Hi @NU2vba, thank you for taking the time to reply to my post and for the guidance, it works perfectly.

    All the best and kind regards

+ 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] Faster way to find and replace multiple values with corresponding values
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2013, 06:50 PM
  2. find and replace multiple values across multiple worksheets
    By Blake 7 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-13-2012, 10:48 AM
  3. Find / Replace with Multiple Values
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2011, 02:30 AM
  4. Find and Replace values from multiple lookup values
    By Gregula82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2007, 03:12 PM
  5. find and replace multiple values
    By nikki115 in forum Excel General
    Replies: 1
    Last Post: 09-13-2006, 01:41 PM

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