I've had a horrible time performing look up/matches between 2 sheets within the same workbook (when 1 of those sheets is via an export from a mainframe system). I discovered they would not match due to an extra character using "LEN" HOWEVER!!! When I attempted "CLEAN" and "TRIM", they did not work. The extra character remained stubborn and caused my code to NOT find matches when there were clearly matches present.
This code finally worked to correct my main matching column, but now, I've got 2 other sheets with varied columns I need to clean.
Can this code be adjusted to simply "clean the whole sheet" (correct any extra char issues) in all columns present on the named sheet? Either tell it to do the whole sheet or allow me to easily set a range like: A:Z... either is fine, I just need it to clean more than one column at a time and need it to do it for columns holding both numbers and text data...
Desperately need this to make my other code work!
Thanks so much!
This code finally worked to correct my main matching column, but now, I've got 2 other sheets with varied columns I need to clean.
Can this code be adjusted to simply "clean the whole sheet" (correct any extra char issues) in all columns present on the named sheet? Either tell it to do the whole sheet or allow me to easily set a range like: A:Z... either is fine, I just need it to clean more than one column at a time and need it to do it for columns holding both numbers and text data...
Desperately need this to make my other code work!
Code:
Sub Mod_111_12_BOM2TO()
'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!
'Sub EveryCharacter()
Dim i As Long
Dim L As Long
Dim c As Range
Dim r As String
Dim rng As Range
'Range to search/replace
Set rng = Range("G9:G100")
'Every Cell!
For Each c In rng
'Get length of string in cell
L = Len(c)
'If blank go next
If L = 0 Then GoTo phred
'Every character...
For i = 1 To L
r = Mid(c, i, 1)
'If current char is outside 'normal' ASCII range
If r < Chr(32) Or r > Chr(126) Then
'delete it
c.Replace what:=r, replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
End If
'else get next character in cell
Next
phred:
'Get next cell
Next c
End Sub
Thanks so much!