+ Reply to Thread
Results 1 to 6 of 6

Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

  1. #1
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    249

    Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

    Hi Everyone,

    Its been a little while since I've delved into writing/interpreting vba code and I can't quite seem to remember or find a function I'm trying to achieve.

    What I want to develop is a macro that will look down a column and replace any cell value that equals "X" with "Y". All values will be text strings.

    I'm going to be defining a whole bunch of different X->Y, G->F, etc., and I expect this list to grow over time (I have no issue with modifying the code afterwards, I just can't seem to get the proper syntax for the overall operation). Essentially I would like the macro to look through an entire column and change any X's to Y's, any G's to F's, etc. for each term change I define through an if/then (I'm assuming the if/then is the proper method here).

    If possible (bonus function) when new terms arise that are not in my code, I would like the code to indicate them in some way (their cell is highlighted a color or the word "New" appears in an adjacent column in the same row, etc.)

    Can anyone help me out? Do you need any more clarity?

    Thanks in advance,

    -LM

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    05-23-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    249

    Re: Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

    Hi xladept,

    Here is a sample workbook. Please let me know if it has sufficient detail to communicate my goal.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,898

    Cool Hi ! try this !


    A beginner level demonstration as anyone can achieve just using the Macro Recorder :

    PHP Code: 
    Sub Demo1()
        
    With ActiveSheet.UsedRange.Columns(1)
            .
    Replace "Red""Primary"xlWhole
            
    .Replace "Yellow""Primary"
            
    .Replace "Green""Secondary"
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

    But instead of changing the code each time a word mod is necessary
    the better is to use an additional worksheet for listing each inital word and its replacement
    and then it would be easier just with an easy formula to highlight any new word …

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,975

    Re: Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

    Another option for part1
    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,975

    Re: Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

    Or maybe this for both
    Please Login or Register  to view this content.

+ 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. Identify specific text strings in a cell?
    By mnidevil in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-18-2014, 01:55 PM
  2. Replies: 12
    Last Post: 01-13-2014, 09:34 PM
  3. Replies: 2
    Last Post: 03-07-2013, 02:34 AM
  4. Help searching an array of text strings for common strings
    By ABComp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2012, 11:19 PM
  5. VBA Code to find and apply formatting to specific text strings
    By chozen86 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-22-2008, 01:20 AM
  6. Identifying specific text strings in a range
    By steekvey in forum Excel General
    Replies: 2
    Last Post: 05-01-2008, 08:00 AM
  7. Text Strings Swap
    By Mzansi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-29-2005, 07:10 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