Microsoft Office Forums - View Single Post

Stillgate · 06-14-2019, 01:50 PM

Hello, I'm new to making Macros in Excel (and VB), I currently have to change data I get and made the following Macro. Of course, the problem I'm having is that the data length isnt always the same. Can someone help me out fix this range? I change the data in 2 different columns. TIA!

Code:

CRL_FIX Macro
' Fix the SUP/WRK and Telephone data
'

'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "=(RC[-2]&RC[-1])"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C2201")
    Range("C1:C2201").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "CHGEWRK"
    Columns("AI:AI").Select
    ActiveCell.FormulaR1C1 = _
        "=(LEFT(RC[-1],3)&""-""&MID(RC[-1],4,3)&""-""&RIGHT(RC[-1],4))"
    Range("AI1").Select
    Selection.AutoFill Destination:=Range("AI1:AI2201")
    Range("AI1:AI2201").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AI1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "TELEPHONE"
    Columns("AH:AH").Select
    Selection.Delete Shift:=xlToLeft
End Sub