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

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

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

I don't know if this is more efficient than your code or not (probably "yes"
if then number of rows is large), but we can eliminate your looping through
each cell in the column by modifying your code like this...

Sub Stitution()
Columns("A:A").Insert Shift:=xlToRight
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & LastRow)
.Copy Range("A1")
.NumberFormat = "General"
Cells(LastRow + 1, "B").Value = 1
Cells(LastRow + 1, "B").Copy
.PasteSpecial , xlPasteSpecialOperationMultiply
Cells(LastRow + 1, "B").Clear
End With
End Sub

Rick


"Mike H" wrote in message
...
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

Rick,

You're correct your modified code is much faster and I considered something
along those lines but couldn't work out a syntax (and still can't) to meet
the OP requirement

Problem 1: In column A, I want to collect data from column B using
=RIGHT(B1,3).

for only taking the 3 righmost digits. I'm sure it's simple but I can't see it

Mike


"Rick Rothstein (MVP - VB)" wrote:

I don't know if this is more efficient than your code or not (probably "yes"
if then number of rows is large), but we can eliminate your looping through
each cell in the column by modifying your code like this...

Sub Stitution()
Columns("A:A").Insert Shift:=xlToRight
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
With Range("B1:B" & LastRow)
.Copy Range("A1")
.NumberFormat = "General"
Cells(LastRow + 1, "B").Value = 1
Cells(LastRow + 1, "B").Copy
.PasteSpecial , xlPasteSpecialOperationMultiply
Cells(LastRow + 1, "B").Clear
End With
End Sub

Rick


"Mike H" wrote in message
...
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