Forgot the maximum length of three bit

c.Value = Right(c.Offset(0, 1).Value, 3)

Use this line instead

Mike

"Mike H" wrote:

Diana,

This macro takes over from the point where column A is inserted

Sub stitution()
Columns("A:A").Select
Selection.Insert Shift:=xlToRight 'Inserts new column
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("A1:A" & lastrow)
For Each c In myrange
c.Value = c.Offset(0, 1).Value
Next
End Sub

Mike

"Diana Morrison" wrote:

I have a macro that moves a bunch of columns around, formats them, and
inserts a blank column A. That's the easy part :).

Problem 1: In column A, I want to collect data from column B using
=RIGHT(B1,3). I want this to loop through until there is no more data in
column B (length will always vary).

Problem 2: If the number transfered into column A is 001, or 011, I wan to
loose the zeros. Can't seem to make them disappear with formatting, so I'm
thinking now I need if statements to use +RIGHT(B1,1) or (B1,2) under those
circumstances. But maybe I'm making this too complicated.

Any help would be much appreciated.

Thanks,
Diana