New to VBA - Macro to leave original column untouched while altering 2nd column.

[TrueStory]

Hi

Please bare with me, I am new to this forum and VBA. I have been working on a Macro that can be used to do
various tasks that we have found to be extremely tedious at work. The data I am working with is addresses, however
there is no standard column for the data, it may come in column G or F. I want the Macro to insert a column to the right
of this column, copy the information over and then alter the information in the new column only. Leaving the original
column with addresses untouched. Thats where I am stumped, I am not sure how to tell the macro to only alter the data
in the new column. I have made an attempt as can be seen in my code however I get run-time error 13 on line
Columns("clean").Select .

My macro so far is:


Sub CleanMe()
Application.ScreenUpdating = False
'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert
' This module will populate the column to the right,with the same data found in the active cell
Selection.Copy
Cells(ActiveCell.Row, 2).Select
ActiveSheet.Paste
Dim insertedColumn As Integer
insertedColumn = ActiveCell.Column + 1
'The inserterColumnIndex value can then be used as follows:
Cells(ActiveCell.Row, insertedColumn).Value = "Clean"
' This module will remove the following characters: + # $ - / \.
Dim e As Object
Columns("clean").Select
With Selection
For Each e In [{"+","#","$","-","/","\"}]
Replace e, "", xlPart
Next
For Each e In [{"&"}]
Replace e, "AND", xlPart
Next
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
Next
End With
On Error GoTo 0
Application.ScreenUpdating = True

End Sub





Any help or tips would be greatly appreciated

 

[b.downey]

The error generated by the stament Columns("clean").Select is due to the fact that "Columns()" expects an argument that translates to either a Column number (i.e 1,2,3, 4...) or a Column Letter ("A", "B", "C", "D"...). The argument "clean" does not evaluate into value that excel can used

I am not exactly sure what the your code is doing, but the following may help solve your task

Sub CleanMe()
    Dim ws As Worksheet
    Dim c As Range
    Dim DupCol As Integer
    
    Set ws = ThisWorkbook.ActiveSheet
    DupCol = 7 'Column G
    
    'Application.ScreenUpdating = False
    'Insert column to the right
    ws.Columns(DupCol + 1).Insert
    
    'This module will populate the column to the right,with the same data found in the active cell
    ws.Columns(DupCol).Copy Destination:=ws.Columns(DupCol + 1)
    ws.Cells(1, DupCol + 1).Value = "Clean"
    
    ' This module will remove the following characters: + # $ - / \.
    
    Dim e As Object
    Dim char As Variant
    Dim I As Integer
    
    char = Array("+", "#", "$", "-", "/", "\")
    With ws.Columns(DupCol + 1)
        For I = 0 To UBound(char)
            .Replace char(I), "", xlPart
        Next
        .Replace "&", "AND", xlPart
    End With
    
    'Application.ScreenUpdating = True
End Sub

 

[TrueStory]

Thank you for the reply, I really do appreciate the help. The idea of the Macro is the user will select the column that contains the data, (in this case specifically addresses).
Running the macro ideally will open a blank column to the right, copy and paste the data into the new column and then remove and "clean" the data as much as possible (i.e
removing odd characters ect). This is what I was trying to accomplish, with my macro.


The problem I have is that when I receive the data it could be in any column due to companies having their own particular formats ect, therefore I can't specify a particular column. This is what made me decide to use the column selected by the user and then to progress from there in way that won't alter the original data.

 

[b.downey]

I made a slight change. You can select any cell in the Worksheet. When you run the macro, it will add a new row next to the ActiveCell and then do the Cleaning

Option Explicit
Sub CleanMe()
    Dim ws As Worksheet
    Dim c As Range
    Dim DupCol As Integer
    
    Set ws = ThisWorkbook.ActiveSheet
    DupCol = ActiveCell.Column
    
    Application.ScreenUpdating = False
    'Insert column to the right
    ws.Columns(DupCol + 1).Insert
    
    'This module will populate the column to the right,with the same data found in the active cell
    ws.Columns(DupCol).Copy Destination:=ws.Columns(DupCol + 1)
    ws.Cells(1, DupCol + 1).Value = "Clean"
    
    ' This module will remove the following characters: + # $ - / \.
    
    Dim e As Object
    Dim char As Variant
    Dim I As Integer
    
    char = Array("+", "#", "$", "-", "/", "\")
    With ws.Columns(DupCol + 1)
        For I = 0 To UBound(char)
            .Replace char(I), "", xlPart
        Next
        .Replace "&", "AND", xlPart
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[TrueStory]

Works like a charm, thank you so much.

 

[b.downey]

No problem. Thank for the feed back