Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem populate data when change from row1 to row4


Hi experts again,

based on changing  the original code to work from row1 to row4 becomes the project doesn't work correctly in second file .

as in ORIGINAL file  when select date from combobox1 and match with header will populate data in listbox and if I select ZERO from combobox2 will popuulate data for date is existed in combobox1 will just show data for cells contain zero or empty and if I select NOT ZERO from combobox2 then will popuulate data for date is existed in combobox1 exclude cells contain zero or empty , and if clear combobox2 will populate whole data whether zero,empty cells or not .

but the second file will not show data correctly .

the only thing what I want , code changes from row1 to row4 .




Tubrak. I can see what's meant to happen and think the problem lies with the indexing of your arrays. Don't have time to check and fix it today but might over the weekend
John_Ru (rep: 6142) Mar 29, '24 at 3:24 pm
Add to Discussion


Selected Answer


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
    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"))
                ' 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
    End With
    With Me.ListBox1
        If UBound(b) = 1 Then
            .Column = b(1)
            .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.



it's perfect 
I note  to doesn't re-autonumber in first column in listbox when search  data like 1,2,3 !
thank you so much.
tubrak (rep: 24) Mar 30, '24 at 6:14 pm
Glad you like it! Thanks for selecting my Answer, Tubrak.. 
John_Ru (rep: 6142) Mar 30, '24 at 7:16 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login