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:
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?
.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:
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: