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


http://www.vbaexpress.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAT8AAACfCAYAAACPzd7xAAAAAXNSR0IArs4c6QAA AARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAABcwSURBVHhe7Z17kBzVdcZntUj w1DEVIGxFbAeEKkESBHvFQiEVkhaCQnMwxDHdiUxEjaCVKqcVOyigp0icf5IlZfYgCkHG4hjxwn ECcESQUIgCWxeEsgCC7FCD7DQCwEyIIREmJP7dffZOXP3ds/M7uzM7Pb3Vf3orbt2/f 82ZHTFTmHjREgETwIzr5dTO6 Wc TfI9EtvlM4rbpTZV/15AJQ3kCtvaCjnXLSgX5yd0Dn/Gscf9Zvbb789WF4Pqum72dcnw4BLsvh8Q5jRdY10dF4pZ1zwWZlw1nwZN3WujD 9K2GeFCbM Ir8wYXgOjlz7lel65q/kFtvvVUeXfOMbH9th7z77ruEEDLk2P7qa7Jy1RPyg7t/KnMv 5JMnX5ZZICFQiEywIIa37QFS Qbt9wmPa9sC3ZECCFDlZc3b5Gv3/wPMu3iq LIb2pkfnHE942/uy14EiGEDBfm7SLAS10EOEcKp8y8Lnqry4iPEDLcQQQ497IvyoSzXOR3btcSuevH/x1sSAghw40f3P0TOX36QilMX7Ak nAj1IgQQoYb BDk3M7LpTDr8iX8VJcQkhvwKfCMrs9JAf uLdSAEEKGK/i3uDQ/QkjuiM3viiXBSkIIaRXGjhsvS5ctC9YB1I0bf1KwLkRN5jd27Fi59tprg3WzZs2KQPruuO/gW136aRYJwYwy233BKsBy 88EJwnMuXL4/uFWnU4378NgMhbW50zArGEWpXCYw96759dB7SxlUruA9dC/UA94Mx6jj7Oy8WzI8 YzI0gLl98vjjgwaYVZfGzPlX12Z WHxYhLZcF2U9F/xAwHjswsZCTzMDjNk3N2zeehueJc1k7HV1Tv02g0G97xdzj3n110l/UfML1VWLvybI0CRkcv0xPjBzXo3mh43iGwny9X61HwiIDOxYsswPmx5jt2Uhg68n1ZgfaJT5Ya7 qEU0B9KNrJG3Oa4XmRyzW7PprfKBm88Pi9hcRNqk1nJD5oA3Qchy1XDc8 vXbAdSHypHWch0Trq1lABvR5v1NhDzKNe PHf3qOfZ6OmZ7rp/XtsAarH OYs0PR3tO6F71HC1Xs0Fb3Ie20Xpgy7Wt1ul92 cF9P51boC/BhS9h5DZoC/7LP05SauzzwB1Wu4/a5TZPgDa6DNWcH/Vzq 2s/W2Tst07khjUNPrr/GB2Pwuvz5Y6aOLEA9aNxEWABaTbgyU2TTqQgsDZbYcad28AAtOF6ddcKF6YK9jr691tm8ftNX7Q Z 4J63Te/Y3i KPLTRWkLaBLegfdYq2D7XDePz7VOz9oB9Np2Hbo1 7 VGu49V0Vn/aFth tc7Ooe0rqy40dzoWbR8CbXR cL69L/s8cbTXtmsJ7exY0E7XUqXrk8GjHubXOQ//zq9G87OLKlRm0zjqwrH45f5CsosVbe3CBXpdW2Y3hl4fVDI/uxHSrgPQv9 PP26bR1vkFR2vf46CMWAsmse1dY5wtH1pO6TtxgU6t/48pGGfhT8GW1 pPzuPobx/3/a5ZNXZZ63tbL0P2qMd0PHifPts7dhs/4peJ ue0LftkzQGNT4cbTrUNot mZ msRB0cdmNYdM46say OX 4sd1tA/N28UcWrC6EP1NmrVRFJzrn6fl9jroB PAvSPvj1vzaGc3je3HP0dBe 3X5jEuvbdQO6TRp96jzm3ofkJoe6T9vrUeY6/UH owDh tt2mA8eq1supCc4d6vV LPkek7Xhxvp1D9K3Px/avaFvbLpTHeRhT1ryQ hEyu/4aYGR F/fD/LDw8NBDC82m0S60MFCm52reLmYssNDi1sWPersI0Vbz9vrAX7Ah0B73pxtOCW0Mf PofaA8tDlRj3LtR9v4oE97fe3bvx c74/TtsFRx4S2mk7Dtke/uK7WoVzz9ho agJ OdrrWO24tb2dk7Q6 wz0GhiLpi22LebTjte2x7X0GeKoaWDXkm0XyiuhsZD6kmVy/THAznlXwfyq/8BDF5bmNW03hr9JsFiwOICW44h22gagP21nF5iWAd0gAH1oub2ef33dTMCO34J Ue X6z1rvaL1tlzvE X2mhiLnTtt42PnCdh79ecGdWoAivZv59aOA/hz7rcH2Pz2HC3359ViDcOCceo56Mveo72/rLq0uUMbba/lduzoz44XaZSjDc6149U6YM/x29m8tgd2vKT vPPOO9E/YM4yN9ThH0KH6kIk5ldd5EfIQIBJhMpBVh0hAAYYKu8vsfld8dVgJSH1hOZHWonOLpofaRA0P9J K0PwIIbmEb3sJIbkk cDjK8FKQggZrsRve2l hJCcQfMjhOSSmV1X0PwIIfkjMr/ZND9CSM6g RFCckmv VEUReVJND KonKp AOPz16XZCmKovKhs2dcQvOjKCp/ovlRFJVL0fwoisqlEvNbnGQpiqLyIZofRVG5FM2PoqhciuZHUVQuRfOjKCqXovlRFJVLReY367J FSZaiKCofovlRFJVL0fwoisqlaH4UReVSND KonIpmh9FUbkUzY iqFyK5kdRVC5F86MoKpei VEUlUvR/CiKyqVofhRF5VI0P4qicqmWNL8Pdt0q 587Rt5ed4wc3PGtpJSiKKp Sszv2iTbfB3ae6/sf3aEfLS1IP/3SkF t3aEHHRlg6dlsrhQkIJh8bKkqm7CNTqke3OSjfKL3X HsDZ3S4c/USjz72vZ4tLc2vZp5YmWLTbloWtR1ADVcua3/7mxcnhTmxSd cm2Njn8Upu8vXZcUjsY8owp2pT1Nibf/IaBygxps3R3OLPq6PDMz9533CY Ja08UWSiND9qcNVS5ndwzz3yztp2KW5xxrfFGeArwEV/T7fLwd33JK3qLd YkNcNHKrTfJLuLkUwHaWGRmgX15fa9O1n8eKO3voo6kH7sg1v kk1glAb039Ht7MaP69tkvN6xwWF2mYJ7Y354YXEjHVztzNHdJ5WHuecGbo vDa9SiunqBqVmN Xk2xz9faz4 TDjc7wNoNCTE9BDr3YJm89OVjRX7zBde9hI5abQl8ziPNIG5PJjBgr9xNt/iTiibqM0qX YIjxpWAOtq Swm1M/5H8fHxebx734ZmibZsttC NudzUnBLjSiuPk8k9pJlcWjlF1aizZ8yXQuelzTc/RHbvPOWiPmd2xR5nfji 7CJApF9uk/2/HCEHdw5G9Bdv8DjqMWYWCXVZppVW56vafrLSZoyOvvs/rY3tB6ol79dVEtr33/zKymly1CCrZczvbRfZHX4hNrripoJ0XTxJ5jmKLzkTdBxa3yZvrBmM6M9ucP/vT7UYg8nr36xA1Fm1/VSTTlNaG7 8lnxan2lCexP9egbWa27B8m53brl5l aPouqvljC/g7vulf1PuKjPmZ7gww5ndqeddprjVBGY38aYt1e1y4Hf1jv68zZ42dtNzwyxaVONIcsostpWk47 HURYt9VFam6xrx8p 21veNltob8yvz1wmfaWVW6VFfmnlFFWjWsL89j0xTg4970wvMTnZ2CanTJokkxzFF10Z I2L/ta56O xk5Kz6qW GxyRSK8BRIanUchi07ayqVjBYNBHbDK2bTVpJxtNWoOxCrbxx XnIZTpeZXaZgntvbGZ Ssz5rRyFc2PGmQl5vdnSbbxOvj6PbJ/tYv6nLlFJGY3ceJEmThhghRfcHm8Hd7gTNEd33rERX vDdYnvxRF5UVNN783Vrmoz0V0MDx50R2d2cHkTj755AiYXnFDm0iEi/6eaZO9ywfz3/1RFJUHJeb3p0m2sTq44155a2V7ZGowvNjgYsaPHyfjxo2T4npXt96V/doZ4/POCB1vPtQu721n9EdRVP/VVPPD3 8OPe1M7dfO1JTE7MaMGRMhkeE580uMT1z6g1 1yZ4VZya9UBRF1a7I/GY1wfzwd7u3lrdHZhdHdDC4mOJzBTnxxBPlhBNOiNLF51y5o7jOmZ97iyzu MaD7XJo76qkN4qiqNrUNPPbu2K8HHoqNjpFcITBuePo0aMjYHbFtbHpFZ91RweOB9e0yd6VM5Pe KIqialNTzO/A9nvkzYeOSKI4xZkaTM4Bc/vUp46X44//ZJQuPgNcG3eUZ Nj8WkX/f1XuxzcxeiPoqja1RTz2/vQOHl/9QhndM7InJlpRKd5mNtxxx0nxx57rEuXzC5KP53wVEE eAyf/HYmvVIURVWv AOPhY0zv/e23SO7f96emFxsbL3A4CLaZOqkT8gfTvo9KTqTE/f2GBSfdCaYIE 6sl8VZM997fL TkZ/FEXVprMvbLD57XnwJDmw0kV9kfE5Q4O5JZGcmhzSvQaHozO5oqN0dOW/BC76W9Emux66OOmdoiiqOjXU/N7b4qK48wJgdzU2B2scHFUV1M0RkcTC42O8cTLv2EOz7ujgm7/ 0Ief91Rn8URVWvhprf7gdc1LfCRX2J4Wk0F0dy7ujMLTY7mJwanQLDAy6txzWgTQ4 PEJ2/YJ/ 6Moqno1zPwQ9e362cjI3IrJW9ayaA5HZ2hFx/b722TCmI9FbL/PlUcm51gdmx2ORaRXu/Sq LjzX46QAzsY/VEUVZ0aZn67/tNFff/roj6N4uxb1940jm1yy6J2GTVqlIwcOVL 9svuHDW9VcAZH3jMlRneXzpCdj7A6I iqOrUEPN7r8dFfT89omR2icmVRXS9tMn2f2 T8aPbZfyn22Xbz1zZY7HxFR916TJc Up3THj9Ry76 y2jP4qiKqsh5rf7gTPlvV MiN qugiu9y0rjkk0Fx1dBFeK6Fw 1exwTHjE5Vc4HmmT9/9nhOz4OaM/iqIqqyHm9 oPR8ZmF5mcA aGSA4GV2ZycboIg4vSDjU4PUa4NAxvhTsud8de2uSVfxqVXJWiKCpdDTE/fBK7//722Oh6zS459kZy7qjGZo RyTnKTE5xbR52x4dxbJO3ftIuO 5j5EdRVGUl5vcnSXZwdPh322X3g52y/c6Rsu3OUbLt yNdGkfHHSNl6 2jZOsdjts/JltvGyVbvgdcuaa/OyqK6Ppwa0KSh/HhWv1S9BXwtXxle5bs178Hvtq9KvlfId/ffmqRf82w n4lfz3HVd0Y6qOsa/X3vmodf3Xth8 ct44aYn5DQfjdjsWLF4d/T6Jm1WMxNWNBVnPNwR5XI 97MK5Va595m/PWEc0vEn5BDK kbhH0/nIZlCyK7tKP7ZR O6eKuqgjf2EhH7ePf4gn/tU17aP0yh4qS mnT3n5uLIN3Y7H/kATZOswVnvN0O8CV7q235/KlvtjsLLX98eJFy/7g/NW2edVfoaQ6SNz7EnakT7v/jlZ1zF5R1PnvPf8 Jql fbz2iY5r6zvUNvmKDa/BTk3P7zlTR4s3l6U1kjyALUg sUxfbtRqc5bnMlTjt6 lC7gCW1DfUB9ld5Li2v iC4 qrPv1414i7sT8vmTWu7GuH 4vL08ZgVemeyzd9SZXOq/wM4z7Sxl7 PMvPCynrfsPX8fu0 ax7SOsvLq92zsPjsfPt5wP9V/msGiman1PpAUcZs5jtIoNsvtq6rHMSRYvPLeCItLZZ/VRb56tSPzqmmHhaKp1Tz/6sKvUdOgeq5by0OqSrGTsUKrPy6/tzHf cevZn1d/zs/J XfNE84seRvkDTnv1z36IaXVZ5zj1eVVMa1uHOvu7vtEqrqUfVS3nDLQ/q/70DdVyXlqd304VKvfKhtWcqyqdn5VP67PxovmVRXqxSpEgHlQpRC/7MfOKdaGHjbcOXshvrx9FgGmLpDyf/bYi/bxyeeMpu35grJGy s qS svawzlqv6ey1XtW7D0Z1jl2CNljyX7ftOu4/dp81l1VY47dc77M55Y/X1WjdTZF87Lt/mVjM4ID8u8Skd/nI1evfs 8Ky6OO8/bOT9SEDz5X94xthQHi ijH5Srw35eU9lkYn3h29bN Bo2CnYn1PWGMqE/vT8jOv0UfZ5lZ hU9rYbd/JQip/bgENpTmveTwqlOl5ldo2R7k3v2xlPajWeYjUICiKhqzJUcNNNL9M0fzyJzzXOGLp846AGlaKzG/mgi8lWYqiqHyI5kdRVC5F86MoKpei VEUlUvR/CiKyqVofhRF5VI0P4qicimaH0VRuVRkfp00P4qiciaaH0VRuRTNj6KoXIrmR1FULkXzoygql6L5 URSVS9H8KIrKpWh FEXlUpH5zbzkC0mWoigqH6L5URSVS9H8KIrKpWh FEXlUjQ/iqJyKZofRVG5FM2Poqhcqtf89u/fTwghuYHmRwjJJTQ/QkguofkRQnIJzY8QkktofoSQXELzI4TkEpofISSX0PwIIbkkMr rL/njYCUhhAxXIvP7j/MWBisJqYUNr74p59 xWY7 1kYpfP1F4pj/wy3RvITmqxL79u2TTZs2yYYNG2T9 vXE0dPTE81LaL5qJTa/KR3BSkKqBRscpvfNFXvkzQMfykcffZR7MA/fefwNOfqbG2s2QGxwmN6uXbvkww85nwDzsGfPnmhe6mGAkfn985QpwUpCqmX j7ZI9 P7okV61FFH8WiONy/fHUXEoXlLAxHO3r17o/NJOTt37owi4tC81UJkfv9I8yMDBG/xEOnohicxmI83DxyOor/QvKWBt3iHDx8O9pl3MC I/kLzVgs0P1IXYH6hhUpiMD heUsD5hfqh8RgfkLzVgs0P1IX1PwY ZWj80Hzqy91Mz/ zY8MFEZ 2dD86kvdzI f9pKB0tTIb kiKRQKvXR095j6pbKo0CHdPXF 6SLXZtFSUz 4tG7kVz4v6WU 1bQZfOpnfvx3fmSANCvy6 nucIa3SJb2lvVId4c1OLNZYZINND4Lza1M38 H94kIHSnMgPRhfYiD3d0tFriLpZcbQm2RiGduSHfBJRh15MNN1dirwXLdVzB5e6md9Mmh8ZIE2J/MpMzhJHf/FGjDdoR8gkG0hrml9ibGWU5gl/Iojn0L7I ObnzlFjjP780JgXGJofaRmaEvllmp 3QR3lfwtsDEM38ivNm2JfTPoaYSg/eND8SMvQlMgvbbOlvu1tzMYMMTTNLzRfWW3Szqk/ND/SMjQl8nOkfeBRivLMhsTbso5u6eltO/gM3cjPn8dQG6RLbaJn0aD5pfmRlqE5kV9MbIClt2cV/6lLgw0QDD3zc0QRtM6rH0mX0osW6fz7/Q0e9TO/ Z8PVhJSLc2K/Fqd1o386kHIQBsDzY 0DM2M/IYCNL/6QvMjLQO y4/f6tIXzAe 1aVW88O3lvBbXcJgXmh pGXA99XdvHxPtDjVAHmMj99Z80b0jc6heUsD31eH763D aQcfKEpvu8wNG 1QPMjdWHDq/ui6A/fXMxvco7BPOCLTDEvmJ/QvKWh3 SMjc4IMAbzgBeEun6TM82P1ANs8Pl3rJfCDY9JYcmjuefor62W87ufr9n4FGzwdevWyYoVK2T58 uW5Z XKlbJ27dq6GB g RFCcgnNjxCSS2h hJBcQvMjhOSSyPwu7LpGtmzdHmxACCHDjVe2bJWp582RwrmdV8qKlWuCjQghZLjx0PKVMvGMi6Q wdfplcudd/xpsRAghw43u7m4Zc p5Ujjp9C6ZvfCLsnHTwP/VNCGEtDK/2bhJpnUulGPGnC6Fz0yeI5PPu1T 8qa/DzYmhJDhwo1fu0lOPGWafHz0ZGd U bIZybPFvzt769u ra8xAiQEDLMQMQH45t01kw58vcnx Y3dspcGeOiv7HOAKdMW jeAn9B7rzrx7Ji5WrZsnVbsCNCCGl18KkuPtz47vfvcm91F0QR35HO9GB8ODrzmxOZ3xhnfuDkq XNl6vmXyjkXXS4XzLlKZnZdLRdFfK7E3MHgqn4xo7/MuTKVs6bPGQbMJg0hNPd5YW46FzSbLpk6bbZMPGNG9OHGJ8ZMlY9/ jRnfDFHjp4s/w8fV05LV8oz7AAAAABJRU5ErkJggg==

[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.