Adding an asterisk

[av8tordude]

This code finds the 5 best and worst entires. Some of the entires have comments inserted into the cell in Column A or Column S. I want to be able to add an asterisk to the output If any of the 5 Best and Worst entries have comments inserted in the cell either Column A or Column S. How can i modify the code to accomplish this task. thanks

Private Sub BestWorstTrades()    If Not obTrade Then Exit Sub
    
    Const SYMBOL_COL As Long = 1
    Const PNL_COL As Long = 17
    Const GNL_COL As Long = 18
    
    Dim i&, refIdx&, midPoint&
    Dim a, c, Trade
    Dim output As Range
    Set Wks = ActiveSheet
    Set output = Wks.Range("$CK$7:$CN$11")
    
    a = getfilteredData
    Set c = New Collection
    
    For i = 1 To UBound(a)
        If obPnL = True Then
            c.Add Array(a(i, SYMBOL_COL), a(i, PNL_COL))
        Else
            c.Add Array(a(i, SYMBOL_COL), a(i, GNL_COL))
        End If
    Next i
    
    If c.Count > 0 Then
        ReDim a(1 To c.Count, 1 To 2)
        i = 0
        
        For Each Trade In c
            i = i + 1
            a(i, 1) = Trade(0)
            a(i, 2) = Trade(1)
        Next Trade
        
        a = ARRAY_heapSort(a, 2)
        output.ClearContents
        
        For i = 0 To 4
            If LBound(a) + i <= UBound(a) Then
                If a(UBound(a) - i, 2) > 0 Then
                    output(i + 1, 1).Value = a(UBound(a) - i, 1)
                    output(i + 1, 2).Value = a(UBound(a) - i, 2)
                End If
                
                If a(LBound(a) + i, 2) <= 0 Then
                    output(i + 1, 3).Value = a(LBound(a) + i, 1)
                    output(i + 1, 4).Value = a(LBound(a) + i, 2)
                End If
            End If
        Next i
    End If
    
    UpdateBestWorstLabels
    
    Set c = Nothing
    a = Empty
End Sub

[June7]

Concatenate. Use an If Then or an IIf() to conditionally include asterisk. Are those columns part of the source array? Want to provide workbook for analysis?

[av8tordude]

I tried but i get an error Application-defined or object-defined error

For i = 0 To 4
    If LBound(a) + i <= UBound(a) Then
        If a(UBound(a) - i, 2) > 0 Then
            Dim commentA As String
            commentA = IIf(Not Wks.Cells(i, "A").Comment Is Nothing Or Not Wks.Cells(i, "S").Comment Is Nothing, "*", "")
            output(i + 1, 1).Value = a(UBound(a) - i, 1) & commentA
            output(i + 1, 2).Value = a(UBound(a) - i, 2)
        End If
        If a(LBound(a) + i, 2) <= 0 Then
            output(i + 1, 3).Value = a(LBound(a) + i, 1)
            output(i + 1, 4).Value = a(LBound(a) + i, 2)
        End If
    End If
Next i
End If

[June7]

I think you would use IsEmpty() function. https://www.techonthenet.com/excel/formulas/isempty.php

Cells object has to use numbers for row and column index references, not letters.

Cells(i, 1)

Cells(i, 19)

[Aflatoon]

Cells can use letters or numbers for columns. What it can't do is use 0 for the row number as is the case in that code.

[June7]

I stand corrected. Could have sworn I had seen error messages when I tried letter references in the past.