Forum Discussion
Sorting list of values by adjacent column text
Hi,
I am looking to create a vba macro button to sort through a single column range (in the example image it would be column "C") and copy corresponding adjacent cell values (Column "D") to another range area. The range area that the cell values are to be copied to is limited to 23 cells, once the 23 cells are fill the macro will need to continue copying the cells into the next specified range. These areas are grouped into pairs and named as different sheets. The macro will need to stop copying the adjacent cells onto the sheet when the text in column "C" changes. Ie S8 and S9 cannot be on the same sheet as one is a Wall and one is a truss in column "C".
Sub sort() Dim i As Integer Dim j As Integer Dim z As Integer Dim w As Integer Range("G5:MM27").Clear w = 7 z = 0 j = 5 For i = 4 To 300 If Cells(i + 1, 3).Value = Cells(i, 3).Value Then If z = 23 Then If w Mod 3 = 1 Then w = w + 1 ElseIf w Mod 3 = 2 Then w = w + 2 End If j = 5 Cells(j, w).Value = Cells(i, 4).Value j = j + 1 z = 1 Else Cells(j, w).Value = Cells(i, 4).Value j = j + 1 z = z + 1 End If Else If z = 23 Then If w Mod 3 = 1 Then w = w + 1 Else End If j = 5 Cells(j, w).Value = Cells(i, 4).Value Else Cells(j, w).Value = Cells(i, 4).Value If w Mod 3 = 1 Then w = w + 3 Else w = w + 2 End If j = 5 z = 0 End If End If Next i End SubMaybe with these lines of code which seem to work in my spreadsheet. Click the button in cell S2 to start the macro. The macro currently works for entries in range C4:C300 and can easily be adapted to other ranges.
7 Replies
Sub sort() Dim i As Integer Dim j As Integer Dim z As Integer Dim w As Integer Range("G5:MM27").Clear w = 7 z = 0 j = 5 For i = 4 To 300 If Cells(i + 1, 3).Value = Cells(i, 3).Value Then If z = 23 Then If w Mod 3 = 1 Then w = w + 1 ElseIf w Mod 3 = 2 Then w = w + 2 End If j = 5 Cells(j, w).Value = Cells(i, 4).Value j = j + 1 z = 1 Else Cells(j, w).Value = Cells(i, 4).Value j = j + 1 z = z + 1 End If Else If z = 23 Then If w Mod 3 = 1 Then w = w + 1 Else End If j = 5 Cells(j, w).Value = Cells(i, 4).Value Else Cells(j, w).Value = Cells(i, 4).Value If w Mod 3 = 1 Then w = w + 3 Else w = w + 2 End If j = 5 z = 0 End If End If Next i End SubMaybe with these lines of code which seem to work in my spreadsheet. Click the button in cell S2 to start the macro. The macro currently works for entries in range C4:C300 and can easily be adapted to other ranges.
- OliverScheurich
Thank you! Looks to be exactly what I was after
- Can you attach a sample file?
Have you considered just writing a formula?Hi there,
Yea I could use cell formulas if that works. The cell formula would need to work on a much larger scale though then what I have shown as an example. That was why I was thinking vba macros may be better suited?
Hopefully the link below works for you, the folder contains the example I have shown on the original post. I cant use an actual example as the document would contain company IP.
https://textormetal.sharepoint.com/:f:/s/Hytek/EjyZ4vmTMQVGoOzGXjzkt9cB2adRt3JiI3HFmnk4jAqclQ?e=nohZen
Hello.
I'm not sure what the selection criteria is. Do the values in column D change. so far I just went and
=D17down the first column as that is 23 lines as per original instructions.
