RicardoS
New Member
- Joined
- Jul 4, 2022
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
I have written a clean function that Returns a string with Purely Printable ASCII characters and it work well when I call it with a cell:
Both RemoveNonASCII($B10) & RemoveNonASCII(@$BH:$BH) work.
But the moment I try to pass a string from a Hlookup() Funcion it fails with a #VALUE! Error
This Fails: =RemoveNonASCII(HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE))
Note: HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE) Gives a String Value.
The Question is simple Why?? does if fail and how can I fix this? I'm pulling in data from other files so I have to do the Lookup...
Here is the Code:
Both RemoveNonASCII($B10) & RemoveNonASCII(@$BH:$BH) work.
But the moment I try to pass a string from a Hlookup() Funcion it fails with a #VALUE! Error
This Fails: =RemoveNonASCII(HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE))
Note: HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE) Gives a String Value.
The Question is simple Why?? does if fail and how can I fix this? I'm pulling in data from other files so I have to do the Lookup...
Here is the Code:
VBA Code:
Function RemoveNonASCII(ByVal TxtString As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
Dim i As Integer
Dim IndexChar
Dim CleanString
'Remove Nonbreaking Space Characters?
If ConvertNonBreakingSpace Then TxtString = Replace(TxtString, Chr(160), " ")
i = 0
While i < Len(TxtString)
IndexChar = Mid(TxtString, i + 1, 1)
'If Char Good, add it to the string
If 31 < Asc(IndexChar) And Asc(IndexChar) < 127 Then
CleanString = CleanString & IndexChar
End If
i = i + 1
Wend
RemoveNonASCII = WorksheetFunction.Trim(CleanString)
End Function