How to loop through a cell three times to do a multi find & replace?

Question

I'm trying to do a Find/Replace and I'm getting stuck. Basically, I want to copy the contents of a cell, let's say it is cell P6. I want to put the conents of that cell 8 cells to the left.

So, in CellAddress of P6 I have the following: 1. Campaign Name = YES 2. Campaign / Program Manager = YES 30. Creative Indicator = YES Q1. New Legal Approval = YES Q2. Targeting Indicator = YES

In cell H6, I want to see this: 1. Campaign Name 2. Campaign / Program Manager 30. Creative Indicator Q1. New Legal Approval Q2. Targeting Indicator

Basically, anything with "YES", "NO", or "=" is replaced with "". I see what the problem with the loop is...I just can't seem to fix it.

Here is my non-working code.

fndList = Array("NO", "YES", "=")
rplcList = Array("", "", "")
    For x = LBound(fndList) To UBound(fndList)
        ActiveSheet.Range("AA1").Value = Replace(ActiveSheet.Range(CellAddress).Value, fndList(x), rplcList(x))
        ActiveSheet.Range(CellAddress).Offset(0, -8).Value = ActiveSheet.Range("AA1").Value
        ActiveSheet.Range("AA1").Clear
    Next

This is the problem. On the first iteration through the loop I get this: 1. Campaign Name = YES 2. Campaign / Program Manager = YES 30. Creative Indicator = YES Q1. New Legal Approval = YES Q2. Targeting Indicator = YES

On the second iteration, I get this: 1. Campaign Name = 2. Campaign / Program Manager = 30. Creative Indicator = Q1. New Legal Approval = Q2. Targeting Indicator =

On the third iteration, I get this: 1. Campaign Name YES 2. Campaign / Program Manager YES 30. Creative Indicator YES Q1. New Legal Approval YES Q2. Targeting Indicator YES


Show source
| excel-vba   | vba   2017-01-04 23:01 1 Answers

Answers ( 1 )

  1. 2017-01-04 23:01

    Get that string once and work on it, i.e.:

    Dim str as string: str = ActiveSheet.Range(CellAddress).Value
    For x = LBound(fndList) To UBound(fndList)
        str = Replace(str, fndList(x), rplcList(x))
    Next x
    
    ActiveSheet.Range(CellAddress).Offset(0, -8).Value = str
    

    However, be aware that this will not replace the patterns as "whole words", they will be replaced even inside other words if they appear.

◀ Go back