Storing items in an array

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

Many thanks for your help, Bryan.

As you suggest, I will try to test it on a smaller sample.

Originally Posted by Bryan Hessey

Hi,

Just curious, how many rows do you have, and how many do you expect to find?

also, simply do

For i = 1 to 100

to check it works (it did for me)

then 1000, 10000 20000 etc

Cheers
---

The sheet has about 25000 rows, so I set up the For loop to go just past the end.
I think there should probably be around 3000 bold items in the sheet.

I haven't had a chance to try a smaller set yet - hopefully I'll get some time to try it later.
Thanks for your help.

Bryan,

I've just tried it for the first 100 rows and it didn't work, so I had another look at the worksheet.
It turns out that the items I thought were in bold are in fact not in bold - they're just in an unusual font.

So the macro should be working okay.
It's been counting up blank cells which were set as bold for some reason (I don't know why.)

As you can probably tell, I'm pretty much a beginner at this stuff - so sorry for my misunderstanding of the problem (my bad.)

Thanks again for your help.