How to adjust this code to cover the whole sheet? Clears Hidden Character Issues

C

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
601
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!
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Change this line
Code: 
Set rng = Range("G9:G100")

TO

Set rng = activesheet.usedrange
 
Upvote 0
Hi Michael,
I attempted what you suggested, but it errors out at this line (highlights this line)
"Run Time Error 13: Type Mismatch"
Any ideas?

Code: 
        L = Len(c)

Here's the full code that I'm using so you can see it in full context:
Code: 
Sub Mod_12_BOM2TO()

'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!
'.......where ghost spacing was preventing other code from locating a match when
'.......it tried to compare to Part Numbers...
'.......TRIM and CLEAN would not clear the characters but this will - but is limited it only one column

     
    Sheets("TO").Select
 
    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("G8:G5000")

    'ALTERNATIVELY, TO DO THE ENTIRE ACTIVE SHEET USE THIS LINE OF CODE INSTEAD:
    Set rng = ActiveSheet.UsedRange
 
     '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
     
    
'--------------------------------------

'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH
'OF THE MATCHES FOUND.
'IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'ROLLED UP ON THE PARTS SHEET WHILE USING THE INVENTORY SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.

    
    Sheets("BOM Worksheet").Select
    
    Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    
    
    'Range("E5:E100").NumberFormat = "General"
    Range("E5").Select
    Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7)"


    
  Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
'--------------------------------------

'LOOKS THROUGH COLUMN E FOR ANY ZEROS AND COLORS THEM RED IF FOUND
'Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("E5:E" & lRow)
    Dim cel As Range
    For Each cel In MR
        If cel.Value = "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 0, 0)
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
            Exit Sub
            
        ElseIf cel.Value > "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 255, 255)
        MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")


        End If
    Next
    Application.ScreenUpdating = True

    
End Sub
 
Upvote 0
Ok, try
Rich (BB code): 
Sub Mod_12_BOM2TO()

'GETS RID OF GHOST CHARACTERS THAT TRIM AND CLEAN WOULD NOT CLEAR!!!
'.......where ghost spacing was preventing other code from locating a match when
'.......it tried to compare to Part Numbers...
'.......TRIM and CLEAN would not clear the characters but this will - but is limited it only one column

     
    Sheets("TO").Select
 
    Dim i As Long, L As Integer, c As Range, r As String, rng As Range
 
     'Range to search/replace
    'Set rng = Range("G8:G5000")

    'ALTERNATIVELY, TO DO THE ENTIRE ACTIVE SHEET USE THIS LINE OF CODE INSTEAD:
    Set rng = ActiveSheet.UsedRange
 
     '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
     
    
'--------------------------------------

'THIS ONE WORKS GREAT FOR COMPARING THE 2 SHEETS, LOOKING FOR PART # MATCHES, IF FOUND, IT WILL SUM TO TOTAL QTYS OF EACH
'OF THE MATCHES FOUND.
'IF IT FINDS PART #12345 WITH 10 AND FINDS IT AGAIN WITH 5 AND FINDS IT A 3RD TIME WITH 5 IT SUMS THE TOTAL TO "20"
'ROLLED UP ON THE PARTS SHEET WHILE USING THE INVENTORY SHEET AS JUST A LOOKUP TABLE WHILE SUMMING.

    
    With Sheets("BOM Worksheet")
    
    .Range("E5:E100").NumberFormat = "General"
    .Range("E5").FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC16,TO!R8C7:R100C7)"
    'Range("E5:E100").NumberFormat = "General"
    .Range("E5:E" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(TO!R8C2:R100C2,'BOM Worksheet'!RC[11],TO!R8C7:R100C7)"
End With

    
  Dim Nmbr As Double
Application.Calculation = xlCalculationManual
For i = 2 To WorksheetFunction.Count(Sheets("BOM Worksheet").Range("P:P")) + 1
    Nmbr = Sheets("BOM Worksheet").Range("P" & i)
    For Each cell In Sheets("TO").Range("B8:B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        If cell.Value = Nmbr Then
        Sheets("BOM Worksheet").Range("P" & i).Font.Bold = True
        cell.Font.Bold = True
        End If
    Next cell
Next i
Application.Calculation = xlCalculationAutomatic
'--------------------------------------

'LOOKS THROUGH COLUMN E FOR ANY ZEROS AND COLORS THEM RED IF FOUND
'Sub ChangeColor()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Range("E" & Rows.Count).End(xlUp).Row
    Dim MR As Range
    Set MR = Range("E5:E" & lRow)
    Dim cel As Range
    For Each cel In MR
        If cel.Value = "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 0, 0)
        MsgBox ("Zero values were discovered. Do not delete these, they'll be used during File Mtc")
            Exit Sub
            
        ElseIf cel.Value > "0" Then
            cel.Resize(, 1).Interior.Color = RGB(255, 255, 255)
        MsgBox ("No Zero values were found. Proceed with your TO to BOM validation.")


        End If
    Next
    Application.ScreenUpdating = True

    
End Sub
 
Upvote 0
I don't get the error, it runs fine
Sorry, I can't go to hosting sites, maybe someone else can jump in and have a look !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
How do i open a csv file created in excel and do a find and replace of this file and save as a new file without strange characters ??
Replies
1
Views
630
Special-K99
S
A
  • Question
how to add my signature with images to macro
Replies
1
Views
182
Domenic
D
S
  • Solved
Display data of Specific Sheets on Listbox Userform
2
Replies
11
Views
882
Akuini
Akuini
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
115
VanGirl
V
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
351
sspatriots
S

Forum statistics

Threads
1,215,400
Messages
6,124,702
Members
449,180
Latest member
craigus51286

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top