Recorded vba for "replace text" does not work

123excel

New Member
Joined
Jan 18, 2017
Messages
34
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:

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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,303
Office Version
2019, 2016, 2013
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,667
Office Version
365
Platform
Windows
I am intrigued, and this may be totally irrelevant ...
Excel returns #DIV/0! when dividing by zero, so why #DIVISION/0! ?
 
Last edited:

123excel

New Member
Joined
Jan 18, 2017
Messages
34
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

Moderator
Joined
Oct 23, 2004
Messages
10,303
Office Version
2019, 2016, 2013
Platform
Windows
what language is in installed in ?
 

123excel

New Member
Joined
Jan 18, 2017
Messages
34
Swedish.. and when you say it, yes this is probably the cause! Nice thinking Mole999.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,016
Messages
5,447,889
Members
405,470
Latest member
manaya

This Week's Hot Topics

Top