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
Bookmarks