Loop to move #'s from one column to next

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diana Morrison - 12 Mar 2008 15:44 GMT

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

Reply to this Message

Mike H - 12 Mar 2008 16:14 GMT

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

> I have a macro that moves a bunch of columns around, formats them, and
> inserts a blank column A. That's the easy part :).
[quoted text clipped - 12 lines]
> Thanks,
> Diana

Reply to this Message

Mike H - 12 Mar 2008 16:37 GMT

Forgot the maximum length of three bit

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

Use this line instead

Mike

> Diana,
>
[quoted text clipped - 28 lines]
> > Thanks,
> > Diana

Reply to this Message

Rick Rothstein (MVP - VB) - 12 Mar 2008 18:37 GMT

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

> Forgot the maximum length of three bit
>
[quoted text clipped - 40 lines]
>> > Thanks,
>> > Diana

Reply to this Message

Mike H - 12 Mar 2008 19:08 GMT

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

> 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
[quoted text clipped - 59 lines]
> >> > Thanks,
> >> > Diana

Reply to this Message