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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.