I have a piece of code that I written that searches column O for a value of 1 and when found it checks the value in column N [offset(-1,-1)] to see if matches certain criteria. If does not then the range is resized to 6 rows cut and then pasted after the first example of a 6 is found in column N.
However When I have more than 6 rows it only moves these and does not go back to the original start point, in this case i!
I have tried both storing i as the start point and selecting it again but this has not worked. Have also tried adding in a count for the resize as the value in column is a "-" value. If I could count this I could use the value for my resize property. Whenever I have tried to do this I get stuck in a loop though!
My code so far is this...
The two lines indicated with a comment '***' are the places I have had trouble with. Annoying part is that my code moves the reference of i when I cut and paste and just picks up from where there not where i was originally!
Sample data...
Columns L, M, N and O are aid columns to help me determine what needs to be moved and where to. Ultimately column M will be in groups of 6 as well as column N, below example.
Aiming for...
You will see that rows 17 and 18 have been moved up completing a group of 6 in column M.
Currently my code reaches row 6 in the above performs well adding in the cut data below row 19 but then picks up from row 19, not 6.
If I could find an easy way of adding some sample data or an image I would to aid assistance. Am really hoping this make sense
Thanks for reading and thanks in advance for any assistance given.
Steven
However When I have more than 6 rows it only moves these and does not go back to the original start point, in this case i!
I have tried both storing i as the start point and selecting it again but this has not worked. Have also tried adding in a count for the resize as the value in column is a "-" value. If I could count this I could use the value for my resize property. Whenever I have tried to do this I get stuck in a loop though!
My code so far is this...
Code:
Sub DynamicSort()
Dim ii As Integer
ii = 1
Range("O6:O74").Select
For Each i In Selection
If i = 1 Then
If i.Offset(-1, -1) <> 6 And i.Offset(-1, -1) <> "-" Then ' check performed in column N
i.Offset(0, -5).Select ' Selects cell in column J
Selection.Resize(6, 7).Cut ' Resizes from column J *** Need to add a count from here until column N does not equal "-",
' could use this number for resize value? Have tried a loop until range <>"-" but get stuck in a loop.
i.Offset(0, -1).Select ' Moves to column N to search for the first instance of a 6 in the column to insert after
Range(Selection, Selection.End(xlDown)).Select
On Error GoTo 999 ' Error handler in case there is no 6 in the range
Selection.Find(What:="6", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
ActiveCell.Offset(1, -4).Select ' Offset back to column J to insert cut data
Selection.Insert Shift:=xlDown ' *** or after doing this go back to where i was originally? ***
999
Else
End If
End If
Next
End Sub
The two lines indicated with a comment '***' are the places I have had trouble with. Annoying part is that my code moves the reference of i when I cut and paste and just picks up from where there not where i was originally!
Sample data...
Code:
| Col I| Col J| Col K| Col L| Col M| Col N| Col O
| Row| Code|Indicator|Counter S| S = 6| C = 6|Counter C
______________________________________________________________________
| 1| 10| S| 1| 1| |
| 2| 20| S| 2| 2| |
| 3| 20| S| 3| 3| |
| 4| 30| S| 4| 4| |
| 5| 40| C| | -| 1| 1
| 6| 40| C| | -| 2| 2
| 7| 40| C| | -| 3| 3
| 8| 40| C| | -| 4| 4
| 9| 40| C| | -| 5| 5
| 10| 40| C| | -| 6| 6
| 11| 40| C| | -| 1| 7
| 12| 40| C| | -| 2| 8
| 13| 40| C| | -| 3| 9
| 14| 40| C| | -| 4| 10
| 15| 40| C| | -| 5| 11
| 16| 40| C| | -| 6| 12
| 17| 60| S| 5| 5| |
| 18| 60| S| 6| 6| |
| 19| 60| S| 7| 1| |
| 20| 60| S| 8| 2| |
Columns L, M, N and O are aid columns to help me determine what needs to be moved and where to. Ultimately column M will be in groups of 6 as well as column N, below example.
Aiming for...
Code:
| Col I| Col J| Col K| Col L| Col M| Col N| Col O
| Index| Code|Indicator|Counter S| S = 6| C = 6|Counter C
______________________________________________________________________
| 1| 10| S| 1| 1| |
| 2| 20| S| 2| 2| |
| 3| 20| S| 3| 3| |
| 4| 30| S| 4| 4| |
| 17| 60| S| 5| 5| |
| 18| 60| S| 6| 6| |
| 5| 40| C| | -| 1| 1
| 6| 40| C| | -| 2| 2
| 7| 40| C| | -| 3| 3
| 8| 40| C| | -| 4| 4
| 9| 40| C| | -| 5| 5
| 10| 40| C| | -| 6| 6
| 11| 40| C| | -| 1| 7
| 12| 40| C| | -| 2| 8
| 13| 40| C| | -| 3| 9
| 14| 40| C| | -| 4| 10
| 15| 40| C| | -| 5| 11
| 16| 40| C| | -| 6| 12
| 19| 60| S| 7| 1| |
| 20| 60| S| 8| 2| |
You will see that rows 17 and 18 have been moved up completing a group of 6 in column M.
Currently my code reaches row 6 in the above performs well adding in the cut data below row 19 but then picks up from row 19, not 6.
If I could find an easy way of adding some sample data or an image I would to aid assistance. Am really hoping this make sense
Thanks for reading and thanks in advance for any assistance given.
Steven