Searching for Paren

[duffs]

Two questions.
1. When using the replace method, what does LookAt do/mean? The only documentation I can find tells me to use either xlPart or xlWhole but doesn't say what these things mean.

2. I'm trying to search for "(" and replace it with "B" for all the cells in my active worksheet. Here's my code:

Sub cleanuptext()
ActiveSheet.Cells.Replace What:="(", Replacement:="B", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

I get this error:
"The formula you typed contains an error..."

I finally figured out that the problem is the paren because if I search for A and replace it with B, it works fine. I tried to escape the paren by:
ActiveSheet.Cells.Replace What:="\(", Replacement:="B" etc.

No errors, but it does not do the replacement.

Any ideas?

TIA, Susie

 

[TonyJollans]

Hi Susie,

1. xlPart means look for the string as part of a cell's contents. xlWhole means look for the string as the whole of a cell's contents.

2. Your code works for me, but what exactly is your error - it looks like an Excel, rather than a VBA, error?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

The Range.Replace method always deal with Formulas, not Values, of the Cells.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

This looks like a version thing. I get the error in Excel 2K, but not in Excel 2K3. XP seems to just do nothing and issue no error message when there are formulae in the sheet. Investigating....

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[duffs]

Ahhhh........I know the problem.

Sorry, I didn't give you the full picture. The first part of the macro does stuff that I'm not familiar with because I didn't write it. It turns out it adds a column that has a formula in it.

I was just trying to add some data cleanup to the end. So I was trying to replace (, but now there's a formula in a column, so it is trying to replace a ( that exists in the formula, hence the error.

Thanks for you help. Knowing that replace works on formulas as well was helpful. And thanks for telling me what xlPart and xlWhole mean.

Is there a link that anyone knows of where I can get more helpful documentation than what Excel offers? It seems that anytime I look at the Excel VBA help or the object browser, it gives me "you can use these values" but doesn't tell me what the values mean. It takes me way more time researching on the web than it should.

Thanks for your help!!
--Susie