Snasui Excel Forum

Code: Select all

Private Sub obMain1_Click()
    Dim i%, iRow%, bNum%
    Dim cSubCode As New Collection
    
    ' Variables
    Set wsSTDB = ThisWorkbook.Sheets("9sd")
    With wsSTDB
        iRow = .Range("b" & .Rows.Count).End(xlUp).Row
    End With
    If obName = "" Then obName = obMain1.Name
    bNum = Right(obName, 1)
    Debug.Print bNum, IsNumeric(bNum)
    
    ' Enable some controls
    cbbSub1.Enabled = True
    cbbSub1.Clear
    
    'Call SubCodeUnique(iRow, bNum)
    On Error Resume Next
    With wsSTDB
        For i = 5 To iRow
            If bNum < VBA.Left(.Cells(i, "b"), 1) Then Exit For
            If bNum = VBA.Left(.Cells(i, "b"), 1) Then _
                cSubCode.Add .Cells(i, "b").Value, .Cells(i, "b").Value
        Next i
    End With
    On Error GoTo 0
    
    'Add item(s) to combo box
    If cSubCode.Count = 0 Then Exit Sub
    For i = 1 To cSubCode.Count
        cbbSub1.AddItem cSubCode.Item(i)
    Next i
    
End Sub