VBA replace multiple strings from a separate list

Question

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

Can you just add an example of what you have and one of what you want ? because it's not cristal clear here ... — smagnan
– smagnan, Commented Aug 1, 2014 at 6:30
What do you mean when you say "fails"? Do you have wrong results or do you get an error (if so, where?) — Noldor130884
– Noldor130884, Commented Aug 1, 2014 at 6:31
There is an excerpt from a table s2 = Sheets("ListToReplace"): — LearnForever
– LearnForever, Commented Aug 1, 2014 at 6:32
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. Try it please and let us know if it helps. — Maco
– Maco, Commented Aug 1, 2014 at 7:14

Maco · Accepted Answer · 2014-08-01 08:55:25Z

2

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.

answered Aug 1, 2014 at 8:55

Maco

42913 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA replace multiple strings from a separate list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related