Results 1 to 6 of 6

Thread: Recorded vba for "replace text" does not work
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Recorded vba for "replace text" does not work

    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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,677
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Recorded vba for "replace text" does not work

    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 ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,194
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Recorded vba for "replace text" does not work

    I am intrigued, and this may be totally irrelevant ...
    Excel returns #DIV/0! when dividing by zero, so why #DIVISION/0! ?
    Last edited by Yongle; May 17th, 2019 at 09:03 AM.

  4. #4
    New Member
    Join Date
    Jan 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recorded vba for "replace text" does not work

    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.

  5. #5
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,677
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Recorded vba for "replace text" does not work

    what language is in installed in ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  6. #6
    New Member
    Join Date
    Jan 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recorded vba for "replace text" does not work

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •