Query AS400 and unknown character
I've been given a new job assignment, so I'll have to pull away from this
for a few days, will try soon, and post another reply later.
I will add though, that while I thought the < worksheetfunction.clean was
working, apparently it is not. The Cells in column 'A' (refer to earlier
post on 11/30/03), while appearing to display the correct value, are still
not recognized by the VLookup function.
Gratefully,
D.S.
"Donnie Stone" wrote in message
...
Don,
Try this solution. It was posted a while back.
Sub trythis()
Dim s As String
Dim c As Range, myRng As Range
Set myRng = ActiveSheet.UsedRange.Columns(2).Cells
For Each c In myRng
With c
s = CStr(.Value)
s = UCase(WorksheetFunction.Clean(WorksheetFunction.Tr im(s)))
.Value = s
.Rows.AutoFit
End With
Next
End Sub
"D.S." wrote in message
...
Great, that works for column 'A', is it possible to use this somehow to
clean column 'B' during the query process and just eliminate the need
for
column 'A' ?
D.S.
"Paul Robinson" wrote in message
om...
Hi,
Try
Application.worksheetfunctions.Clean(Trim(QueryVal ue))
The Clean removes non printing characters and the Trim leading or
trailing
spaces.
regards
Paul
"D.S." wrote in message
...
I'm using Excel to query an AS400 library file. The query is
returning
a
combination of numerical and alpha-numerical values. When I use
VLookup
to
find specific data that was returned by the query, the VLookup does
not
recognize the values as they "appear" to be. There is apparently an
additional character in the value that is not apparent. I've tried
searching the data for the infamous Alt-0160 character, but I'm not
finding
that. Any ideas as to what's going on, or how to compensate for it?
D.S.
|