No column insertion in Copy code

**flebber** · 07-26-2010, 08:16 PM

I am having a little issue with inserting columns in recorded code.

my aim is to insert 4 columns to the right of Column "AH" rename columns and then use first 4 delimited entries from AH one for one into each of the columns. This is the code I am using, the actual code for moving delimited characters was written by Paul from here http://www.excelforum.com/excel-gene...-one-cell.html

This i my current code.

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("Table1[[#Headers],[career]]").Select
    ActiveCell.FormulaR1C1 = "car_earn"
    Range("Table1[[#Headers],[Column4]]").Select
    ActiveCell.FormulaR1C1 = "car_start"
    Range("Table1[[#Headers],[Column3]]").Select
    ActiveCell.FormulaR1C1 = "car_first"
    Range("Table1[[#Headers],[Column2]]").Select
    ActiveCell.FormulaR1C1 = "car_second"
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "car_third"
    With Sheets("Sheet1")
    arr = .Range("AH:AH" & .UsedRange.Rows.Count)
    For i = 1 To UBound(arr)
    .Range("B" & i & ":E" & i).Value = Split(arr(i, 1), "-")
    Next i
    End With

It does rename AH but then errors. When I look at the sheet I notice the columns aren't inserted. I recorded a macro to insert columns and that is what I have copied into my above code.

**flebber** · 07-26-2010, 08:25 PM

actually resolved my first issue but I am recieving a range issue in the ubound function

[CODE]Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Table1[[#Headers],[career]]").Select
ActiveCell.FormulaR1C1 = "car_earn"
Range("Table1[[#Headers],[Column4]]").Select
ActiveCell.FormulaR1C1 = "car_start"
Range("Table1[[#Headers],[Column3]]").Select
ActiveCell.FormulaR1C1 = "car_first"
Range("Table1[[#Headers],[Column2]]").Select
ActiveCell.FormulaR1C1 = "car_second"
Range("Table1[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "car_third"
With Sheets("Sheet1")
Dim arr As Variant, x As Long
arr = .Range("AH:AH" & .UsedRange.Rows.Count)
For x = 1 To UBound(arr)
.Range("B" & x & ":E" & x).Value = Split(arr(x, 1), "-")
Next x
End With/CODE]

Column AH is the row which the data lies in.

**flebber** · 07-26-2010, 11:07 PM

I am on a roll I have solved largely my previous problem however it has created two new issues.

The first cell I am splitting contains the data for example

22-4-3-7 $217560.00

It is now copying the data across but not removing itself from the current cell. Also when 7 is copied it copies the dollar value after it, I wanted the dollar value to remain in the intial column AH.

I notice also that using the above formula changes the number to a text format. I know by recording code that the two number formats I would need are but where is appropriate in the formula.

Selection.NumberFormat = "$#,##0.00"
Selection.NumberFormat = "0"

Edit am investigating using a Do loop for the number format conversion.

**flebber** · 07-27-2010, 04:37 AM

Sorry I know the question is confusing. I guess what I am trying to do is cut and paste delmited numbers to new colums except if number preceeded by "$" then format as currency and format other numbers from text to numbers.