CODEWITHSUNDEEP
excelvba
JK THE GREAT
JK THE GREAT

Reputation: 1

I need help truncating characters beyond 40 in the one column in VBA

My code fails to truncate anything beyond 40 characters when i run it. Any suggestions on what line I can use. The code is an xlam. I am trying to truncate anything in column G I tried to put in formula left(F2, 40). Maybe i am using a wrong formula? or there is another way to fix it. Please let me know. Here is the Code I have so far:

 Option Explicit


    Private Sub ProcessReport()       
       
       Dim oWB As Excel.Workbook
       Dim oXLAM As Excel.Workbook       
       Dim oWS As Excel.Worksheet        
       
       Set oWB = ActiveWorkbook
       
       Set oWS = ActiveSheet
       
       Set oXLAM = Workbooks("NRPPosPay.xlam")
       
       Call formatcols  
    End Sub

    Sub formatcols()
          Dim oWS As Excel.Worksheet
          Dim LastPopulatedRow As Long
          LastPopulatedRow = Range("G" & Rows.Count).End(xlUp).Row
    'Delete Colums
        Columns("F:H").Select
        Selection.Delete Shift:=xlToLeft
    
    'Move Colums
        Columns("E:E").Select
        Selection.Cut
        Columns("B:B").Select
        Selection.Insert Shift:=xlToRight
        
        Columns("G:G").Select
        Selection.Cut
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        
        Columns("G:G").Select
        Selection.Cut
        Columns("C:C").Select
        Selection.Insert Shift:=xlToRight
        
        Columns("E:E").Select
        Selection.Cut
        Columns("D:D").Select
        Selection.Insert Shift:=xlToRight
        
        Columns("G:G").Select
        Selection.Cut
        Columns("E:E").Select
        Selection.Insert Shift:=xlToRight
        
        Columns("G:G").Select
        Selection.Cut
        Columns("F:F").Select
        Selection.Insert Shift:=xlToRight
        
        
    'Replace in Colums
       Range("G:G").Replace What:=",", Replacement:=" "
       Range("C:C").Replace What:="-", Replacement:=" "
       Range("E:E").Replace What:="OCK", Replacement:="IS"
       Range("E:E").Replace What:="VCK", Replacement:="CN"
       
    'Formula in Columns
      Range("G2").Formula = "=left(G2, 40)"
      Range("G2: " & "G" & LastPopulatedRow).FillDown
          
    'Copy and Paste
       Range("G:G").Copy
       Range("F:F").PasteSpecial _
       Operation:=xlPasteSpecialOperationDivide
      
       
     'Delete Column
      Columns("G:G").Select
      Selection.Delete Shift:=xlToLeft
    'Replace
       Range("F:F").Replace What:=",", Replacement:=" "
       
     
    'Format columns
       Columns("A:A").NumberFormat = "0"
       Columns("B:B").NumberFormat = "0"
       Columns("C:C").NumberFormat = "#.00"
       Columns("D:D").NumberFormat = "mddyyyy"
       
       
    ' Delete Header row
    
       Rows(1).EntireRow.Delete
    End Sub
    
    Sub ProcessPos(control As IRibbonControl)
        Call ProcessReport
    End Sub

Upvotes: 0

Views: 87

Answers (1)

Anmol Kumar
Anmol Kumar

Reputation: 182

Leaving all other changes that you can do to improve the code you should replace this line

Range("G2").Formula = "=left(G2, 40)"

with this one:

Range("G2").Value = Left(Range("G2").Value, 40)

This would make your code do what you desire for cell G2 now you can use loop to do this for all cells.

Upvotes: 3

Related Questions