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

[Marc L]

If you want to skip blank cells, just use <>"" rather than >""

No matters as non blank is always superior to empty (> "") …
​

 

[Eloise T]

No matters as non blank is always superior to empty (> "") …
​

So....
(>"") is non-blank?
(<>"") is not empty?
Correct? ...or do I have that backwards?

 

[Marc L]

​

As both mean the same : not "blank" or not empty …​

​

 

[p45cal]

As both means the same : not "blank" or not empty …

Almost:

 

[Marc L]

Sorry as I thought it was on VBA side and not on Excel side, like this :​

Sub Demo1A()
    V = "A"
    If V > "" Then Beep
End Sub

Sub Demo2A()
    V = "A"
    If V <> "" Then Beep
End Sub

Sub Demo1N()
    V = 1
    If V > "" Then Beep
End Sub

Sub Demo2N()
    V = 1
    If V <> "" Then Beep
End Sub

Sub Demo3N()
    V = 1
    If V Then Beep
End Sub

 

[p45cal]

Yes, all instances of such code were in Evaluate statements so we are referring to the 'Excel' side, so the two are not the same.
I raised exactly this problem in msg#12.

 

[Chirag R Raval]

Dear All,
Sory Sor hacks, but

@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:

        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)

Is this code , if it processp same cell, 2 times, & first if part change /replace char(160) with "", then
Second if part, (isdate), really recognise that same cell as like date? Because, if till there
Leaving "" in the cell, then ,splitrd parts also can have "" within it, means "", also need to remove from this
Cell, after 1st if part of code, then rearrange this parts in that cell as require format..without any "" in it.

Also, This is just my guess, base on isdate can only work if "/" within cell, but may be it can not
Recognise properly, & hence parts after splitting may have some other char with it..

May be there are .calculate require after 1st if part do it's it'?

Please don't feel anger, i not raise any dispute here, it is just try to learning purpose only to understand things & logic of the code.

Apologies if i heart any one here.

Regards,

Chirag Raval

 

[Chirag R Raval]

Dear Sirs, @p45cal , @Marc L , @Debaser , @Chihiro , @narayank

Can i i ask is this thread suddenly closed without any final solution?
Is above code really need to further cleaning after isdate part?
Is trim clean also properly work on numbers as string?

Hope there are someone who can reply.

Regards,
Chirag Raval

 

[Chihiro]

*shrug* I never participated in thread.

I assume OP never followed up or OP's issue was resolved.

It is generally recommended, if you have your own question related to OP's, to start a new thread referencing this thread. With clear outline of your specific issue in relation to this.

 

[Chirag R Raval]

Dear All Experts,

@Marc L , @p45cal , @Chihiro , @shrivallabha , @Debaser

This thread is outstanding, repeately refer whole thread give us new dimension of knowledge on each
Repeatation.

Mainly learnable thing is power of Evaluate.
Oh process range in one shot without loop...just amazing...

Dateserial open the door on another dimension that how can we set
Date in proper form.
All experts, your contributions in this thread also outstanding .

I didn't know about which is finally solved or not this thread but I want to also thanks
@Eloise T for generate this thread.

Hope learners found this thread as most knowledgeable.

Regards

Chirag Raval