Replace text or leave it alone pls.

BDD · Feb 7th 2017

Working on a code to replace specific text with other text but it is clearing all cells even if its not in the list.

How would I tell it to leave the cells alone if the and values in place if the change doesn't apply?

For example - If I am telling it to replace HORSE with COW, it is also removing the DOG.

The particular part of the code I believe is the culprit and that I am unable to figure out is -

[TABLE="width: 326"]

[tr]

[td]

For Each c In rngR

[/td]

[/tr]

[tr]

[td]

If Not c.Value = "" Then curVal = c.Value

[/td]

[/tr]

[tr]

[td]

With rng

[/td]

[/tr]

[tr]

[td]

.Replace curVal, c.Offset(0, 2).Value, xlWhole, , True

[/td]

[/tr]

[/TABLE]

mrmmickle1 · Feb 8th 2017

Re: Replace text or leave it alone pls.

With this line of code:

Code

If Not c.Value = "" Then curVal = c.Value

You are essentially saying: If a cell in rngR has a value in it then replace whatever values in Rng have curVal in it with c.Offset(0,2).Value

Is this what you want to do? If not what do you expect it to do? Can you give a before and after example or post a sample workbook with non-sensitive information?

**mikerickson** · Feb 8th 2017

Re: Replace text or leave it alone pls.

Use the .Replace method on the whole range at one go. No looping.

Code

rngR.Replace "horse", "cow"

BDD · Feb 8th 2017

Re: Replace text or leave it alone pls.

Excellent. Thank you mikerickson.

Replace text or leave it alone pls.

Participate now!

Share