Results 1 to 5 of 5

Macro for find/replace specific text within a column

Threaded View

  1. #1
    Registered User
    Join Date
    11-18-2015
    Location
    United Kingdom
    MS-Off Ver
    2010
    Posts
    2

    Question Macro for find/replace specific text within a column

    Hi everyone.
    I have a column B with 726 book titles. I need to find the ones that have country codes in brackets, eg. (ES) and replace the text inside the brackets with a full country name, e.g. (Spain). I came across the macro below. It seems to work, but the problem is, it would only work if I had just (ES) in my cell and nothing else. I need a code that will replace this (ES) for (Spain) and other country names but the book title will remain. For example if the title of a book is: "Workbook Second Edition 1 (PL)" I would like to change it to "Workbook Second Edition 1 (Poland)".

    In Sheet 2, I have created a column A with a full list of country codes in brackets and column B with a full list of the corresponding country names in brackets. These are the values that the macro should look for in my Column with book titles (in sheet 1).

    Could anyone please help me modify this macro or suggest a new one in order to make it work?

    Sub ReplaceValues()
    Dim cell As Range
    
    'will go 1-by-1through the cells in the range A1:A10 on Sheet1
    For Each cell In Sheets("Sheet1").Range("A1:A10")
        'Any value on Sheet2 within the range B3:B15 that matches
        'the value of the current cell being checked on Sheet1
        'will be replaced by the value of the corresponding cell in column B on Sheet1
        Sheets("Sheet2").Range("B3:B15").Replace cell.Value, cell.Offset(, 1).Value, vbTextCompare
    Next cell
    
    End Sub
    Thank you in advance.
    Last edited by majkkali; 11-18-2015 at 10:54 AM. Reason: problem solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  2. [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
  3. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  4. Macro to find header information and replace with specific text
    By Nancy Taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2013, 04:47 PM
  5. Need a Macro to do a find and replace for multiple text in just one column only
    By beepbeep27 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-10-2012, 12:47 PM
  6. Find/Replace VBA for a specific column
    By boolean in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2011, 10:17 PM
  7. macro to replace specific data with column header text
    By wkilc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2010, 11:52 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