Feb 24 2022 02:05 PM
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".
Feb 24 2022 04:09 PM
Feb 24 2022 04:52 PM
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.
Feb 24 2022 05:14 PM
Hello.
I'm not sure what the selection criteria is. Do the values in column D change. so far I just went and
=D17
down the first column as that is 23 lines as per original instructions.
Feb 24 2022 05:29 PM
Feb 24 2022 05:43 PM
Like I said the column G is 23 lines long
I would try something like at G5
=IF(C4="Walls",D4,"")
at J5
=IF(C4="Truss",D4,"")
if you the name in column D changes say from Walls to Truss you would get a blank cell in that line.
Feb 25 2022 04:49 AM
SolutionSub 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 Sub
Maybe 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.
Feb 27 2022 01:22 PM
Feb 25 2022 04:49 AM
SolutionSub 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 Sub
Maybe 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.