Recorded vba for "replace text" does not work

[123excel]

Hi, I have some VBA code that loops through column D and uses the values in range B:X. My problem is that sometimes, the value in some of the cells will have the error #DIVISION/0!. This stops my loop and creates an error. My solution to this was to put the below code before rendering the rest of the VBA code. This replaces #DIVISION/0! with a zero. But the code don't work! It is recorded from when I did it manually and when doing it manually everything works great. But when I run the Macro, the #DIVISION/0! is not replaced.

Anyone have an idea of what to do and why it don't work?

Here is the code I recorded:

Columns("B:X").Select
    Selection.Replace What:="#DIVISION/0!", Replacement:="0", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[mole999]

looks fine, did you directly select the errant value and paste directly into the code,. when you step through with f8 can you see your columns are selected first ?

 

[Yongle]

I am intrigued, and this may be totally irrelevant ...
Excel returns #DIV/0! when dividing by zero, so why #DIVISION/0! ?

 

[123excel]

This is so weird.

For me, Excel is showing #Division/0! and when using this manually it works great. However, after your comment Yongle I changed it to #div/0! In the vba and now it works!

I dont know why this is but this was the solution.

So thanks for your help!

Hopefully this can save the headache for some other people to.

 

[mole999]

what language is in installed in ?

 

[123excel]

Swedish.. and when you say it, yes this is probably the cause! Nice thinking Mole999.