VBA Replace error - string treated as formula

Question

I am trying to clean some text contained in an very large Excel spreadsheet by replacing all instances "<br />" with a single space (" "). I've tried two methods: using the normal Find/Replace All options in Excel, and using this very simple VBA macro:

Sub CleanUp()
    Application.Cells.Replace What:="<br />", Replacement:=Chr(32), LookAt:=xlPart
End Sub

In both cases, I receive an error message that states: "The formula you typed contains an error. For information about fixing common formula problems, click Help. To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group). If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (')."

Of course, I am NOT trying to enter a formula. However, preceding "<br />" with a single quotation mark as Excel suggests does not fix the problem.

Even though my search string doesn't contain any wildcards (as far as I know), I've tried playing around with literal characters in my macro to see if that would help. It doesn't fix the problem, but here's the code anyway:

Sub CleanUp()
  Dim SearchFor As String
  SearchFor = Chr$(60) & "br" & Chr$(32) & Chr$(47) & Chr$(62)
    Application.Cells.Replace What:=SearchFor, Replacement:=Chr$(32), LookAt:=xlPart
End Sub

Useful facts:

I can easily find and replace other strings in this spreadsheet using both the normal find/replace method AND the VBA macro.
I have other similar spreadsheets in which finding and replacing "<br />" is possible.
On my friend's computer it is possible to find and replace all instances of "<br />" using both methods.

Does anyone have ANY idea why this is happening? Does it have something to do with my Excel settings?

Does it work if you try to do this on a single column (instead of all cells)? — enderland
– enderland, Commented Aug 22, 2012 at 2:47
I think you need to isolate the problematic string - presumably at some stage Excel is trying to update a formula which fails. I would delete half the data, run the test and then if the error happens again, keep on deleting and testing, or if the error doesn't happen restore the hald you deleted and then keep on halving and testing that — brettdj
– brettdj, Commented Aug 22, 2012 at 2:53
Thanks for the suggestions! It does work on a single column. But there are no formulas in the spreadsheet at all, so I don't think the problem has to do with a failed updated formula... — patlican007
– patlican007, Commented Aug 22, 2012 at 2:58
What if you format all the cells as Text and then perform the replacement? — Tim Williams
– Tim Williams, Commented Aug 22, 2012 at 4:26

waqasahmed · Accepted Answer · 2012-08-22 11:06:18Z

0

Have you tried:

Cells.Replace What:="<br/>", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

answered Aug 22, 2012 at 11:06

waqasahmed

3,8936 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Replace error - string treated as formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related