1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Eliminating Run-time error '13': while trying to eliminate ASCII 160

Discussion in 'VBA Macros' started by Eloise T, Sep 5, 2018.

  1. Eloise T

    Eloise T Active Member

    Messages:
    813
    I have the following working line of code which TRIMs and CLEANs the data.

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))

    I'm trying to merge the following: SUBSTITUTE(#,CHAR(160),"")
    into the above line to also "weedout" ASCII 160, but the following code, in the VB Editor:

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""))),"""")", "#", .Address))

    returns:

    upload_2018-9-4_19-40-29.png


    I tried putting two "--" in front of SUBSTITUTE as well as a single "-", thinking it was a text vs. numeric issue, integer vs. string, but that didn't work, i.e. ...

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(--SUBSTITUTE(#,CHAR(160),""))),"""")", "#", .Address))

    How do I rearrange the code to eliminate ASCII 160s as well as the run-time error?
    Thanks for looking.

    P.S. I would have uploaded the file but it's too large (over a 1 Mb) and I don't believe it is needed to solve this issue, correct?
    Last edited: Sep 5, 2018
  2. vletm

    vletm Excel Ninja

    Messages:
    4,411
    Eloise T
    What would be # -value in Your formula? SUBSTITUTE(#,CHAR(160),"")
    ( with my Excel, it causes error )
    Have You tried to do this step-by-step?
    a = substitute(#,char(160),"")
    b = clean(a)
    c= trim(b)
    ... and so on?
    and after that You would combine everything in on 'sentence'.
  3. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,920
    You need to apply formula like below perhaps (untested)

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

    Before using Evaluate you can also test your formula by modifying the code like
    Code (vb):
    .Formula = "=" & Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address)


    And then see how it is getting built!
  4. Eloise T

    Eloise T Active Member

    Messages:
    813
    Good news/bad news. Adding two double quotation marks to Ln 107 in VBE:
    upload_2018-9-5_14-44-15.png

    Duplicated below:
    Code (vb):

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))
     
    corrected the Run-time error 13; however, it did not remove the ASCII 160s when running the macro "CleanUp_8."

    I highly reduced the file size so I could upload it here for your perusal.

    upload_2018-9-5_14-49-13.png

    Thank you for having another look.

    Attached Files:

    Last edited: Sep 5, 2018
  5. vletm

    vletm Excel Ninja

    Messages:
    4,411
    Eloise T
    if You would change that """" to char(32) ...
  6. Eloise T

    Eloise T Active Member

    Messages:
    813
    Did you mean from """" to " " ?

    upload_2018-9-5_16-14-11.png
    Last edited: Sep 5, 2018
  7. vletm

    vletm Excel Ninja

    Messages:
    4,411
    Eloise T
    as written """" to char(32)
    ("""" is just above Your arrow)
    ... and soon Your Trim would take care that char(32).
  8. Eloise T

    Eloise T Active Member

    Messages:
    813
    Tried:

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),Char(32)))),"""")", "#", .Address))

    I didn't get an error but it didn't remove the ASCII 160s either.
  9. vletm

    vletm Excel Ninja

    Messages:
    4,411
    If You would left there ONLY that
    substitute-function (which would take care Char(160) ) and
    verify that Your syntax is okay.
    After that add other functions step-by-step and
    always verify that part works too.
  10. Debaser

    Debaser Active Member

    Messages:
    448
    The problem is not with that code, it's with the range you apply it to:

    Code (vb):
    Ws.[A3:F3]
    Eloise T likes this.
  11. Eloise T

    Eloise T Active Member

    Messages:
    813
    You're a genius.
  12. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    @Debaser is quite right, your
    With Ws.[A3:F3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row)
    above that is only looking in column A:F, not G which has your pink dates in. The formula removes the CHAR(160) characters successfully though.

    But… it wouldn't do just to change that to:
    With Ws.[A3:G3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row)
    because the formula you evaluate starts with:
    IF($A$3:$G$9>"",
    and numbers (real dates in cells G5 to G7) are NOT >"". This results in them becoming "" (an empty string, thus losing your real dates)!
    So you'd probably be better treating that little range separately.
    If you were only to remove CHAR(160) from the string, you'd end up with a string that you'd hope Excel properly interpreted as a date. I say 'hope' because there are always problems with leaving it to Excel to interpret what it thinks are dates. Depending on your locale, it often gets the months and days of the month confused.

    So, after your line:
    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))
    you could insert the following:
    Code (vb):
            With .Offset(, .Columns.Count).Resize(, 1)    'date completed column
             rngVals = .Value 'copy values into array in-memory, to work on.
             For i = 1 To UBound(rngVals)
                If TypeName(rngVals(i, 1)) = "String" Then
                  rngVals(i, 1) = Replace(rngVals(i, 1), Chr(160), "")
                  If IsDate(rngVals(i, 1)) Then    'does it look like a date?
                   DateParts = Split(rngVals(i, 1), "/")    'assumes your date strings are going to be / delimited.
                   rngVals(i, 1) = DateSerial(DateParts(2), DateParts(0), DateParts(1))    'converts string to date assuming m/d/yyyy format
                 End If
                End If
              Next i
              .Value = rngVals 'put the updated values back on the sheet
           End With
    And a small thing, that line of code which determines the cells you're looking at:
    With Ws.[A3:F3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row)
    ends up having 2 too many rows (because it starts in row 3, not row 1) so you could tweak that line to:
    With Ws.[A3:F3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row - 2)
    Last edited: Sep 6, 2018
  13. Eloise T

    Eloise T Active Member

    Messages:
    813
    p45cal, Debaser is a genius, but you are a master chess player looking ahead 7 moves.

    You are SO right! I thought the problem could be solved by simply altering A3:F3 to A3:G3 but in doing so I "temporarily" lost all my dates! As Foghorn Leghorn would say, "Fortunately, I say fortunately, I keep my Excel files backed-up for just such an emergency."
    [​IMG]
    I was nearly pulling my feathers out in clumps.

    Thanks to both of you for breathing fresh air back into this problem.

    I'll take a hard look at your code. Thank you for all your effort!

    News at 11.
  14. Eloise T

    Eloise T Active Member

    Messages:
    813
    I changed:

    With Ws.[A3:F3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row)

    to:

    With Ws.[A3:G3].Resize(Ws.Cells(Rows.Count, "A").End(xlUp).Row -2)

    ...and added the following immediately below it:


    Code (vb):

    .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address))

    With .Offset(, .Columns.Count).Resize(, 1)  'date completed column
       rngVals = .Value  'copy values into array in-memory, to work on.
         For i = 1 To UBound(rngVals)
              If TypeName(rngVals(i, 1)) = "String" Then
                  rngVals(i, 1) = Replace(rngVals(i, 1), Chr(160), "")
                  If IsDate(rngVals(i, 1)) Then  'does it look like a date?
                     DateParts = Split(rngVals(i, 1), "/") 'assumes your date strings are going to be / delimited.
                     rngVals(i, 1) = DateSerial(DateParts(2), DateParts(0), DateParts(1))  'converts string to date assuming m/d/yyyy format
                  End If
              End If
         Next i
      .Value = rngVals 'put the updated values back on the sheet
    End With
     
    ...and yet it still deleted all the dates.

    Suggestions?
    Last edited: Sep 6, 2018
  15. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Leave as :F3
  16. Eloise T

    Eloise T Active Member

    Messages:
    813
    I changed :G3 back to :F3. No change for the better.

    I must not have placed the extra code properly.

    I thought I understood. Using the uploaded file, can you re-explain where it goes?

    The uploaded file will work one time. Once it has changed Column G from Generic to Dates, it won't work again.

    Suggestions?
    Last edited: Sep 7, 2018
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Well, I tested it on that file from msg#4 and it worked OK. I even copied your quoted code in msg#14 (which has an extra top line - no matter) and placed it at line 108 here (this is an freshly opened version of your file):
    upload_2018-9-7_21-36-1.png
    It doesn't matter whether it's placed before or after the .Value line.
    (I even tried it in the 'wrong' place around line 137 and it still worked without deleting dates)

    Try adding the code to the file you attached in msg#4, and if it STILL deletes the dates (we're talking about the dates in column G right?) I suggest a brief TeamViewer session.
    Last edited: Sep 7, 2018
  18. Eloise T

    Eloise T Active Member

    Messages:
    813
    I'll give it a try and get back to you. Thanks!
  19. Eloise T

    Eloise T Active Member

    Messages:
    813
    I wrote a message and now it's gone...so here's trying again.

    Please take a look at the uploaded file. It will work one time only. The macro changes the dates which are in Generic format into Date format. If the macro is run a 2nd time the macro deletes all the dates. ...somethin' ain't right. Help...
    I'm all for TeamViewer if you believe that's necessary.

    Attached Files:

  20. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    At line 101 you still have :G3 !!
  21. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    You wrote another and it's gone too!
  22. Eloise T

    Eloise T Active Member

    Messages:
    813
    Here's trying again...

    I apologize. You are absolutely correct. I'm dealing with too many micro versions; however, I have concluded that the "Sanitized" file works just fine (with :F3); however, the "real" file (using the exact same copied and pasted macro code) (and after a couple minutes of crunching) yields:
    upload_2018-9-8_0-30-14.png

    ...and clicking on [Debug] revealed the following:

    upload_2018-9-8_0-27-11.png

    I have a small hunch that once the macro code reaches the tab with the ASCII 160s (number 16 of 18 tabs), it chokes. I only say that because of it taking so long before it chokes.

    Unless you've got a notion of what to try next, I would very much like to use TeamViewer at your convenience.
    Last edited: Sep 8, 2018
  23. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    Re TeamViewer, I'll start a Conversation with you…
  24. Debaser

    Debaser Active Member

    Messages:
    448
    If you want to skip blank cells, just use <>"" rather than >""
  25. Eloise T

    Eloise T Active Member

    Messages:
    813
    Your clever tips are always appreciated. Thank you!

Share This Page