mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,457
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
Code:
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?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,637
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,457
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,637
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,457

ADVERTISEMENT

Yes thats what I need to do.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,637
Office Version
  1. 365
Platform
  1. Windows
In that case what is not working?
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,457
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,637
Office Version
  1. 365
Platform
  1. Windows
Odd, do you have any merged cells?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,156
Members
409,851
Latest member
Ingar
Top