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
Bookmarks