Forum Discussion
Sorting Bins while keeping objects in them
Hello, I am trying to sort a list similar to the following. The bins need to be in order (1A,1B,1C,1D,1E, etc.) while the objects within the bin need to move with them. An example is supplied. Th...
I just figured this out using a series of complicated nested =IF statements, feel free to suggest a better way if anyone knows it
Sub sort()
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim k As Integer
Dim posfind As Integer
Dim lngzeilemax As Integer
Dim search As String
On Error GoTo Endproc
Range("D:D").Clear
lngzeilemax = Range("B" & Rows.Count).End(xlUp).Row
k = 3
For j = 1 To 40
For i = 1 To 26
search = j & Chr(64 + i)
posfind = Application.WorksheetFunction.Match(search, Range(Cells(3, 2), Cells(lngzeilemax, 2)), 0) + 2
If i < 26 Then
For z = 1 To lngzeilemax
If IsNumeric(Left(Cells(posfind + z, 2), 1)) Then
Range(Cells(posfind, 2), Cells(posfind + z - 1, 2)).Copy Cells(k, 4)
k = Range("D" & Rows.Count).End(xlUp).Row + 1
Exit For
Else
End If
Next z
Else
For z = 1 To lngzeilemax
If IsNumeric(Left(Cells(posfind + z, 2), 1)) Then
Range(Cells(posfind, 2), Cells(posfind + z - 1, 2)).Copy Cells(k, 4)
k = Range("D" & Rows.Count).End(xlUp).Row + 1
Exit For
Else
End If
Next z
End If
Next i
Next j
Endproc:
End SubMaybe with these lines of code. You can click the button in cell F2 in the attached file to start the macro.
Can you share your nested IF statements?