PDA

View Full Version : Swapping Cells



Loss1003
07-26-2011, 08:00 AM
I want the adjacent cells in Column A and Column B to swap with the underneath cells in Column A and B if the following condition meets:
If Column C > 1 and Column D > 4

Please, keep in mind in columns C and D are formulas based on the text that appears in columns A and B and will change when the cells are swapped. Additionaly, I would like the code to loop until all the cells in ranges A2:B100 meet the following in columns C & D:
Column C > 1 and Column D > 4.

Loss1003
07-26-2011, 11:17 AM
Sub Swapper()
For I = 1 To Range("A4100").End(xlUp).Row
If Cells(I, 3).Value > 1 and Cells(I, 4).Value > 4 Then
Rows(I).Cut
Rows(I + 2).Insert
I = I + 1
End If
Next
End Sub

the above code swaps the first row with the second however does not repeadetly loop through the rest of the range until the conditions are meet in each cell range.

CatDaddy
07-26-2011, 11:27 AM
not enough info for me...can you post a sample wb? this is definitely doable but i dont really understand what you're getting at

Bob Phillips
07-26-2011, 11:38 AM
Sub Swapper()
Dim i As Long
For i = Range("A4100").End(xlUp).Row To 1 Step -1
If Cells(i, 3).Value > 1 And Cells(i, 4).Value > 4 Then
Rows(i).Cut
Rows(i + 2).Insert
i = i + 1
End If
Next
End Sub

Loss1003
07-26-2011, 12:55 PM
I’m trying to exchange, swap, or move the active cells in a range with the cells directly underneath when the following criteria/condition is meet in column C and Column D

Column C = 1 and Column D > 2

In Column C, I have formula that calculates based on the values in Column A and Column B. I would like the formulas to stay put and not move.

Finally, I need the code to repeat or loop until the values in Columns C and D fall under the following criteria:

Column C <= 1 and Column D < 2

See attached worksheet

CatDaddy
07-26-2011, 02:32 PM
Sub Swapper()
ActiveWorkbook.Sheets(1).Activate
Dim cell As Range
Dim v1 As String, v2 As String, id1 As String, id2 As String

For Each cell In Range("C:C")

If cell.Value >= 1 And cell.Offset(0, 1) > 2 Then
v1 = cell.Offset(0, -2).Value
v2 = cell.Offset(1, -2).Value

cell.Offset(0, -2).Value = v2
cell.Offset(1, -2).Value = v1

id1 = cell.Offset(0, -1).Value
id2 = cell.Offset(1, -1).Value

cell.Offset(0, -1).Value = id2
cell.Offset(1, -1).Value = id1
End If

Next cell

End Sub