VBA Find & Replace

[mikeymay]

I am using the following to replace text strings in a selection but I need to use the Lookin:=xlValues but it doesn;t seem to like it

Selection.Replace What:="*H*", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False

Is there an option to look in values only as the default seems to be formulas?

 

[Fluff]

No there isn't.
The reason being that a value returned by a formula doesn't really exist, so if even if you changed it, the formula would return the original value when it next recalculated.

 

[mikeymay]

Thanks Fluff

The selection I am looking in contains only values and there are no formulas at all.

After a bit of testing, sometimes this works and sometime it doesn't and I'm not sure why.

 

[Fluff]

That code should clear the contents of any cell that contains an h (regardless of case).
Is that what you are trying to do?

 

[mikeymay]

Yes thats what I need to do.

 

[Fluff]

In that case what is not working?

 

[mikeymay]

the removing of any cell contents that contain a h doesn't always execute.

The line of code executes without an error but cells that contain a h are remaining.

 

[Fluff]

Odd, do you have any merged cells?