Selected Answer
Tubrak
In the attached revised file, I've made the changes (and comments) in bold below.
Sub FillListBox()
Dim a, b, x, d As Date, col, s, i As Long
Me.ListBox1.Clear
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
x = Split(Me.ComboBox1, "/")
d = DateSerial(x(0), x(1), x(2))
s = Choose(Me.ComboBox2.ListIndex + 2, "", "=0", "<>0")
With Sheets("stock").[a4].CurrentRegion
col = Application.Match(CLng(d), .Rows(1), 0)
If IsError(col) Then MsgBox "Can not get column matches to " & Me.ComboBox1: Unload Me: Exit Sub
' offset a by a row: was transpose with row(5:
a = .Offset(1, 0).Value: x = Evaluate("transpose(row(1:" & .Rows.Count & "))")
If s <> "" Then
x = Filter(.Parent.Evaluate("transpose(if((row(" & .Columns(col).Address & _
")>1)*(" & .Columns(col).Address & s & "),row(" & .Address & ")))"), False, 0)
If UBound(x) = -1 Then Exit Sub
ReDim Preserve x(1 To UBound(x) + 1)
End If
ReDim b(1 To UBound(x))
For i = 1 To UBound(x)
If s = "" Then
' if no case filter, use original filter
b(i) = Array(i, a(x(i), 2), Format(a(x(i), col), "#,0.00"))
Else
' otherwise keep S.N and use cell values
b(i) = Array(Cells(x(i), 1).Value, Cells(x(i), 2).Value, Format(Cells(x(i), col).Value, "#,0.00"))
End If
Next
End With
With Me.ListBox1
If UBound(b) = 1 Then
.Column = b(1)
Else
.List = Application.Index(b, 0, 0)
End If
.ColumnCount = 3
.ColumnWidths = "40;250;60"
End With
End Sub
Offsetting by a single row skips the header row and the transpose now works from the first row (since with ...row(5... , the first 4 rows were overlooked and the array x was a different size to array a).
In populating b, I added a test (effectively) on the Combobox2 value and -if that's non-blank- use the worksheet cell values (rather than array values). That makes it easy to check which serial numbers are indeed Zero (if you filter by that).
I think this works well with the data starting on row 4 but if you move the header row, don't forget to change the bit in bold in the above code:
With Sheets("stock").[a4].CurrentRegion
and in:
Private Sub UserForm_Initialize()
ComboBox1.List = Filter([if(isnumber(4:4),text(4:4,"yyyy/m/d"))], False, 0)
Hope this fixes your problem. If so, please remember to mark this Answer as Selected.
