CleanTrim function

[Forestq]

hi,

I'm using CleanTrim function wrote by Rick: Trim all Cells in a Worksheet - VBA

but I got Run-time error `1004`
Application-defined or object-defined error
after 1774 recrods

my code below

Dim sourceWb As WorkbookDim LastRow, x_rows As Long


Dim i As Long


Set sourceWb = ActiveWorkbook
Set WS = sourceWb.Sheets("Sheet1")
    
LastRow = WS.Range("A65536").End(xlUp).Row


For i = 2 To LastRow
    Range("S" & i) = CleanTrim(Range("G" & i).Value)
Next i

do you know what is wrong?

 

[nuked]

What is in cell G1774 (or whatever the problem-range is)?

 

[Forestq]

there is text, len(g1774) = 998, there is couple of ENTER/CHAR13 in the text

maybe is other way to clean ?

normally in excel when I used clean function to all rows in column its fine

 

[Forestq]

next record is starting from "==="

when I deleted it the code is working

any idea how to resolve the issue?

 

[nuked]

The next cell is almost certainly causing the problem. Can't you just manually change that partcular record and re-run the routine? Or do you have reason to believe that this problem will repeat elsewhere in the data?

 

[Forestq]

I have a about 20 000 rec and I'm converting the file into CSV and then uploading into ORACLE db

do you know how to replace "===" to "" (blank) ?

 

[Forestq]

Range("O" & i) = Replace(Range("G" & i).Value, "=", "")

this is for all "=" into the cells, but I want to cut the "=" only from begining

 

[Rick Rothstein]

next record is starting from "==="

when I deleted it the code is working

any idea how to resolve the issue?

I think the problem is that you are assigning a text string that starts with an equal sign to a cell that is formatted as "General". Excel uses a leading equal sign as an indicator for a formula, so it thinks you are assigning a 998 character formula when, in fact, it is not a formula. (Unteste) Try formatting your Column S as Text either before you run the macro or with a code line at the beginning of your macro (not my function).

 

[Forestq]

my code

Dim FileToOpen, outputFile As String
Dim i, LastRow As Long
Dim InWb As Workbook, InSh As Worksheet
Dim snapshotDate As String
 
Application.ScreenUpdating = False


FileToOpen = OpenFile("TXT Files", "*.xls", ThisWorkbook.Path & "\DATA\.xls")
 
If FileToOpen <> "" Then     
         
        Set InWb = Workbooks.Open(FileToOpen)
        Set InSh = InWb.Sheets(1)


        LastRow = InSh.Range("A1").SpecialCells(xlCellTypeLastCell).Row


        With InSh
            .Cells(1, 13) = "Header1"
            .Cells(1, 14) = "Header2"
        End With
         
        For i = LastRow To 2 Step -1
            If Range("A" & i) = "ISSUE" Then Range("A" & i).EntireRow.Delete
            
            Range("Z" & i) = Replace(Range("G" & i).Value, "=", "")
                        
            Range("AA" & i) = CleanTrim(Range("Z" & i).Value)
            Range("AB" & i) = CleanTrim(Range("J" & i).Value)
            Range("AC" & i) = CleanTrim(Range("L" & i).Value)
            
            If Range("A" & i).Hyperlinks.Count > 0 Then
                Range("M" & i) = Range("A" & i).Hyperlinks(1).Address
            End If
            
            If Range("C" & i).Hyperlinks.Count > 0 Then
                Range("N" & i) = Replace(Range("C" & i).Hyperlinks(1).Address, Format(Range("C" & i).Value, "#,##0.00"), Range("C" & i).Value)
            End If
        Next i
        
        Range("AA2:AA" & LastRow).Copy
        Range("G2").PasteSpecial
        Range("AB2:AB" & LastRow).Copy
        Range("J2").PasteSpecial
        Range("AC2:AC" & LastRow).Copy
        Range("L2").PasteSpecial
        Columns("Z:AC").Select
        Selection.Delete Shift:=xlToLeft
                        
         outputFile = Left(sourceFile, InStrRev(sourceFile, "\")) & "ABC" & ".csv"


         InWb.SaveAs Filename:=outputFile, FileFormat:=xlCSVWindows
         InWb.Close SaveChanges:=True
            
         MsgBox "File saved as " & outputFile  
 
End If

I need to clean few columns, so I replace "=" to "" , copy column to empty space and then copy back again to proper place (I did it for all my columns which need to be clean)