CODEWITHSUNDEEP
vbaexcel
LearnForever
LearnForever

Reputation: 185

VBA replace multiple strings from a separate list

I need to replace STRINGS but so far I managed to replace words only. The code is going through Description column and is supposed to replace every sequence from the list. The idea is to use abbreviations(new values) rather than complete words (old values).

Every time there is a neighboring (no space) word,character etc, the code fails. What is wrong? Thank you!!!

Sub ReplaceWords_BUT_Need_ToReplaceStrings()

    Application.ScreenUpdating = False

    'This macro works with separated words only. Eliminate hyphens etc before applying this macro.

    Dim r As Range, A As Range
    Dim s1 As Worksheet, s2 As Worksheet, LastRow As Integer, v As String, j As Integer,      
    oldv As String, newv As String

    LastRow = Cells(Rows.count, "A").End(xlUp).row

    Set s1 = Sheets("JE_data")
    Set s2 = Sheets("ListToReplace")
    Set A = s1.Range("J:J").Cells.SpecialCells(xlCellTypeConstants)
    For Each r In A
        v = r.Value

        For j = 2 To LastRow

            oldv = s2.Cells(j, 1).text
            newv = s2.Cells(j, 2).text

'Replace function as you've used it is case sensitive. 'Are you sure that all your invoice words start with capital letter? 'If not then use v = Replace(v, oldv, newv, 1, -1, vbTextCompare) function instead. OK.

        v = Replace(v, oldv, newv, 1, -1, vbTextCompare)    
        'v = Replace(v, oldv, newv)

        Next j
        r.Value = Trim(v)

    Next r

    Application.ScreenUpdating = True

End Sub

Example:

ListToReplace

Left column (old)     Right column (new)
----------------------------------------
Invoice               inv 
Salary                sy 
Credit                cr

JE_data

cr Note
cr note 
INV/2712/14RF/0229/14 
Invoice 1078 10 
TECHNOQ01SI 2014 03288

(Editor note: I don't know if the example is correct since it was written in the comments)

Credit was replaced with cr and Invoice was expected to be replaced with inv but it was not

Upvotes: 0

Views: 2543

Answers (1)

Maco
Maco

Reputation: 429

Replace function as you've used it is case sensitive. Are you sure that all your invoice words start with capital letter? If not then use 

v = Replace(v, oldv, newv, 1, -1, vbTextCompare)

function instead.

Upvotes: 2

Related Questions