Hi.

I'm trying to program a macro that will run down the first column of a worksheet, counting the number of product names it finds.
The product names are the only items on the worksheet that are in a bold font, so I'm using this attribute to identify them.
The macro moves down the rows by incrementing the variable 'rowNo'.
It keeps a tally of the products in 'prodCount'.

The problem I'm having is that I also want the macro to store all the names of the products it finds into a 1-dimensional array called 'prodList'.
At the end of the macro the product count is displayed via a message box, and this seems to be working okay.
But a second message box that is supposed to display the first and last items in the product list array doesn't show any names have been stored.

Have I set up the array wrong?
Or am I storing the product names incorrectly?
Any help with this problem would be appreciated.

Here is the macro code:


Private Sub CommandButton1_Click()
'Count the number of products in the worksheet as bold items

Dim rowNo As Long
Dim prodCount As Integer
Dim prodName As Variant
Dim i As Long
Dim prodList(1 To 5000) As String

rowNo = 1
prodCount = 0

For i = 1 To 25400
rowNo = rowNo + 1

If Cells(rowNo, 1).Font.Bold = True Then
prodCount = prodCount + 1
prodList(prodCount) = Cells(rowNo, 1).Value
End If

Next i

MsgBox ("Number of products=" & prodCount & " rowNo=" & rowNo)
MsgBox ("Product 1=" & prodList(1) & " Product last=" & prodList(prodCount))

End Sub