• 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...

  • 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

Eloise T

Active Member
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:

vletm

Excel Ninja
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'.
 

shrivallabha

Excel Ninja
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:
.Formula = "=" & Replace("IF(#>"""",TRIM(CLEAN(SUBSTITUTE(#,CHAR(160),""""))),"""")", "#", .Address)

And then see how it is getting built!
 

Eloise T

Active Member
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:
.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.
 

Attachments

Last edited:

Eloise T

Active Member
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.
 

vletm

Excel Ninja
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.
 

p45cal

Well-Known Member
@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:
        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:

Eloise T

Active Member
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."

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.
 

Eloise T

Active Member
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:
.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:

Eloise T

Active Member
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:

p45cal

Well-Known Member
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:

Eloise T

Active Member
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...
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):
View attachment 55044
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.
I'm all for TeamViewer if you believe that's necessary.
 

Attachments

Eloise T

Active Member
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:
Top