VBA comboBox not returning second drop down list-CodePudding

Ok, not really sure what I did wrong but when I run it, it gives me an overflow error. The drop down list in the first combo Box works fine, but when I select the "subcat" from the list it gives me the overflow error.

Any clues?

Option Explicit
Private Sub ComboBox1_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Detailed")
Dim i As Integer
Dim n As Integer

n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("3:3"), 0)
Me.ComboBox2.Clear
For i = 4 To Application.WorksheetFunction.CountA(sh.Cells(3, n).EntireColumn)
    Me.ComboBox2.AddItem sh.Cells(i, n).Value
Next i

End Sub


Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Detailed")

Dim i As Integer

Me.ComboBox1.Clear
For i = 1 To Application.WorksheetFunction.CountA(sh.Range("3:3"))
    Me.ComboBox1.AddItem sh.Cells(3, i).Value
    'ComboBox1.Text = "SubCat"
    
Next i

End Sub

CodePudding user response：

Change the declaration of i to be of type Long, since the number of cells in EntireColumn dramatically exceeds the capacity of an Integer (the extant type for i).

CodePudding user response：

There are several issues in the error procedure ComboBox1_Change():

Declare all your Integers as Long to avoid an overflow error 6, which btw is the typical for cases with row values exceeding the positive integer limit of 32 767.
Define your data's HeaderRow, e.g. as constant Const HeaderRow As Long = 3
Extend the loop range by adding the number of rows before the HeaderRow, i.e. HeaderRow - 1:

For i = HeaderRow   1 To Application.WorksheetFunction.CountA(sh.Cells(HeaderRow, n).EntireColumn)   (HeaderRow - 1)
    ' ...
Next i

Further hints

Note that the chosen way to count the number of data rows would ignore empty cells, so that I'd prefer something like

Dim LastRow as Long
LastRow = sh.Cells(sh.Rows.Count, n).End(xlUp).Row
For i = HeaderRow   1 To LastRow
    '...
Next i

As adding each element one by one can be time-consuming,
I'd prefer to avoid a cellular loop and to write the whole column data to a variant datafield array and assign them eventually to the combobox'es .List property in one go.

With sh
    Dim data As Variant
    data = .Range(.Cells(HeaderRow 1,n),.Cells(LastRow,n)).Value2
    Me.ComboBox1.List = data
End With

which could be shortened even to

With sh
    Me.ComboBox1.List = .Range(.Cells(HeaderRow 1,n),.Cells(LastRow,n)).Value2
End With