PDA

View Full Version : VBA Column Looping



yipster
03-30-2010, 01:04 AM
Hello,

I am working with Excel 2007 and need help coding a macro to move some cells around in my worksheet.

Basically I need to work with 2 columns.

The first column needs to be iterated through and cut all cells that have a number between 10000 and 19000 and paste them : 1 cells down, 2 cells left

The second column needs to be iterated through and cut all cells that have the word "bike" and paste them : 1 cells down, 2 cells left

Thanks in advance, Cheers,
yipster

Bob Phillips
03-30-2010, 02:16 AM
Public Sub ProcessData()
Const FIRST_COLUMN As String = "G" '<<<<< change to suit
Const SECOND_COLUMN As String = "L" '<<<<< change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, FIRST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, FIRST_COLUMN).Value2 >= 10000 And _
.Cells(i, FIRST_COLUMN).Value2 <= 19000 Then

.Cells(i, FIRST_COLUMN).Cut .Cells(i + 1, .Columns(FIRST_COLUMN).Column - 2)
End If
Next i

LastRow = .Cells(.Rows.Count, SECOND_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, SECOND_COLUMN).Value2 = "bike" Then

.Cells(i, SECOND_COLUMN).Cut .Cells(i + 1, .Columns(SECOND_COLUMN).Column - 2)
End If
Next i
End With

End Sub