How can i print excel comments to multiple worksheets in a specific order?

Question

I am working on a project in which i would like to list comments in cells on another worksheet in a specific order. In one sheet I would like the comments of:

• Phase5C
• Phase5B
• Phase5A
• Phase4
• Phase3B
• Phase3A
• Phase2A

In another sheet I would like the comments of:

• Phase 3C
• Phase2B

And in another sheet the comment of:

• Phase1

I have currently found and altered the following to read all comments and make 1 list, but the order in which the comments appear is not as I would like.

Could someone please point me in the right direction?

Sub Afgeronderechthoek1_Klikken()
    Application.ScreenUpdating = False

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet
    Dim newwks As Worksheet
    Dim i As Long

    Set curwks = ActiveSheet

    On Error Resume Next
    On Error Resume Next
    Set commrange = curwks.Cells _
        .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
       MsgBox "no comments found"
       Exit Sub
    End If

    Set newwks = Worksheets.Add

     newwks.Range("B1:E1").Value = _
         Array("Comment")

    i = 50
    For Each mycell In commrange
       With newwks
         i = i - 1
         On Error Resume Next
         .Cells(i, 5).Value = mycell.Comment.Text
       End With
    Next mycell

    Application.ScreenUpdating = True

End Sub

Answer 1

It's usually the case that if you want data 'sorted' into a specific order, you will need to define that sort order. It's true that Excel's Sort method is pretty sophisticated and can manage some commonly used sort orders, but your case is not only based on Comments but also very particular. Therefore, the start of your code will need to define the sort order you want. That can be done in a number of ways; a simple one might be just to create an array of the order you want and then search for each array item in turn. It should then be a trivial task of writing your results to your worksheet in that order.

In the code below, I've assumed your search order is as you've listed in your question, and I've only done the first sheet. It shouldn't be difficult for you to expand the principle to other sheets.

I've used a simple Find method but you can use whatever method suits your purpose. You'll need to be watchful of this, though, as even in your question there are typos (such as the space between "Phase" and "3C" on sheet 2, and the lower case "p" at "phase1" in your reference sheet. If your data isn't clean, then you'll need to write code to cleanse it or make your finding routine more sophisticated.

In principle, then, your code structure could look a bit like this:

Dim seq1 As Variant
Dim rng As Range, foundCell As Range
Dim searchText As Variant
Dim r As Long

'Define the sequences.
seq1 = Array("Phase5C", "Phase5B", "Phase5A", _
             "Phase4", _
             "Phase3B", "Phase3A", _
             "Phase2A")

'Acquire the commented cells.
Set rng = Sheet1.Cells.SpecialCells(xlCellTypeComments)

'Loop through the sequence in order
'and write results to Sheet2.
r = 1
For Each searchText In seq1
    Set foundCell = rng.Find(searchText, , _
                             xlValues, _
                             xlWhole, _
                             xlByRows, _
                             xlNext, _
                             True)
    'If there's a match, write it to the sheet.
    If Not foundCell Is Nothing Then
        Sheet2.Cells(r, 1).Value = foundCell.Comment.Text
        r = r + 1
    End If
Next