Results 1 to 6 of 6

Replace string with strings from lookup table

Threaded View

  1. #1
    Registered User
    Join Date
    11-06-2014
    Location
    Germany
    MS-Off Ver
    2010
    Posts
    3

    Replace string with strings from lookup table

    My problem: I have several thousand article with American units and want to convert them using a comparison list.

    Example:
    Red Ball 5 inches long
    Ball Bearing 1/16 in long
    Black Block 1/16 in x 3/8 in
    Ball Bearing 11/16Inches long

    I have a list like this:
    1/16 1,6 mm
    1/16 in 1,6 mm
    1/16 Inch 1,6 mm
    1/16 inch 1,6 mm
    11/16 in 17,5 mm


    I need a macro that compares each column of one table against my conversion table and changes just the exact value.

    If I use this macro with xlWhole it does not find anything, if I use xlPart it replaces 11/11 with the 1/16 value. My Lookup table is around 700 entries.

    Sub MultiFindNReplace()
    Dim Rng As Range
    Dim InputRng As Range, ReplaceRng As Range
    xTitleId = "KutoolsforExcel"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
    Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
    ' Application.ScreenUpdating = False
    For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace What:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlPart
    Next
    ' Application.ScreenUpdating = True
    End Sub
    I would appreciate any help.

    With kind regards, Markus
    Last edited by alansidman; 11-06-2014 at 08:57 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup Table to Find/Replace Every Instance of a String (2/2)
    By Baghel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-04-2013, 05:49 AM
  2. Replies: 12
    Last Post: 12-31-2012, 04:13 AM
  3. [SOLVED] How to lookup and replace string
    By singhabhijitkumar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-18-2012, 04:43 AM
  4. [SOLVED] Find strings within cells and replace with string from a table
    By MMSte in forum Excel General
    Replies: 6
    Last Post: 05-14-2012, 12:22 PM
  5. search for multiple strings and replace them with the same string
    By aaron85 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-14-2009, 04:24 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