VBA Dynamic Replace Function

[datajunkie]

Hi All,

I'm wondering if anybody knows a way to write a VBA script where i can take a list of text values in one column with the text values to replace them in the adjacent column. Then at the top of the matrix i have text paragraph where these values may reside. Any ideas would be much appreciated!

Thanks,
Dan

[HansV]

Welcome to Eileen's Lounge!

Is the following description correct?

A2, A3, A4 etc. contain text strings, and B2, B3, B4 etc. contain other text strings.
A1 contains a longer text. In this text, you want to replace the value of A2 with that of B2, the value of A3 with that of B3, etc.

Or do you want something else?

[datajunkie]

See attached screenshot. Essentially i have 45 cells of code that i need to change different reference url in. I have the list of old urls, list of new ones, and don't want to manually do a find and replace all again and again across the 90 URLs that need to be updated.

Any ideas?

[HansV]

Here is such a macro:

Code: Select all

Sub ReplaceStrings()
    Dim strText As String
    Dim r As Long
    Dim m As Long
    strText = Range("B1").Value
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 3 To m
        strText = Replace(strText, Range("A" & r).Value, Range("B" & r).Value)
    Next r
    Range("B1").Value = strText
End Sub

Please test it on a copy of your worksheet first!

[datajunkie]

You are awesome Hans. And a question. if i have 45 columns containing strings going from B to AV or so would i just modify the code above where range is

Range("B1:AV1").Value = strText

THANK YOU!!!!

[HansV]

That's slightly different - when replacing in multiple cells in a range, it's more efficient to use the Replace method of that range:

Code: Select all

Sub ReplaceStrings()
    Dim r As Long
    Dim m As Long
    Dim n As Long
    Application.ScreenUpdating = False
    ' Last used row in column A
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Last used column in row 1
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    ' Loop through the rows
    For r = 3 To m
        ' Replace text string from row r
        Range(Cells(1, 2), Cells(1, n)).Replace _
            What:=Range("A" & r).Value, _
            Replacement:=Range("B" & r).Value, _
            LookAt:=xlPart
    Next r
    Application.ScreenUpdating = True
End Sub

[datajunkie]

Thanks. I just tested it out and it doesnt seem to be working. Any ideas as to how to modify?

[datajunkie]

I have my data organized as in screenshot. Column B1:BQ1 contain the text where the fields need to be replaced. Then A has original url and B has URL to update to.

[HansV]

You'll have to widen the columns to see the result, or click in individual cells in row 1 and look at the formula bar.

The code works for me - I tested it.

[datajunkie]

What do you mean widen the columns?

[datajunkie]

When I run it, the values in column A are still in B1:BQ1 where i want the values in column B to be the new values in B1:BQ1

[HansV]

Could you attach a small sample workbook to a reply? That would enable me to investigate the problem at first hand.

[datajunkie]

Han's see attached file - if you could please delete after you download that would be much appreciated. Also, i just noticed when i try to use find and replace in excel it wont find the string i want to replace. but when i do just a control find it does work. Im not sure what the issue is here.

[datajunkie]

also - I'm on a Mac. Don't know if that would be an issue.

[HansV]

The text in cells A3, A4 etc. is nowhere to be found in row 1, so there is nothing to replace...

[datajunkie]

so in my range of B1:BQ1 some cells may have one of the url values in A to be replace with B, some may even have multiple. Some might not have any. Does that impact the ability to do this?

Maybe im not explaining well.

The values in Column A are in cells B1:BQ1. The values in Column B i want to replace in any instance when the match in A is found in B1:BQ1.

Does that make more sense?

[HansV]

That's what the code does.
But in your sample workbook, NONE of the values in A3:A5 occur in any of the cells in B1:K1. So in your sample workbook, the code won't replace anything - there is nothing to be replaced.

[datajunkie]

But the values are there. Copy one of the values and do a control find. You will see it's in a cell, but when I try to do a find and replace with the native functionality it does not find the value. I have never seen anything like this. any thoughts? do you think the amount of characters in the cells where I'm trying to perform the replace is preventing the find and replace functionality? I have never encountered anything like this in all my years.

[Rudi]

If the string in column A is not an exact match to the values in B1:BQ1, then no replacing will take place. For instance if you have trailing spaces in the values in column A, but the values in B1:BQ1 do not have that trailing space, it is not replaced. You could try trimming the find value and see if that makes a difference?

Code: Select all

Sub ReplaceStrings()
    Dim r As Long
    Dim m As Long
    Dim n As Long
    Application.ScreenUpdating = False
    ' Last used row in column A
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Last used column in row 1
    n = Cells(1, Columns.Count).End(xlToLeft).Column
    ' Loop through the rows
    For r = 3 To m
        ' Replace text string from row r
        Range(Cells(1, 2), Cells(1, n)).Replace _
            What:=Trim(Range("A" & r).Value), _
            Replacement:=Range("B" & r).Value, _
            LookAt:=xlPart
    Next r
    Application.ScreenUpdating = True
End Sub

[HansV]

I did try to find the value of A3 manually. It was only found in A3. Similar for A4 and A5.