show data on userform vertically based on combobox for multiple ranges

[Omran Y]

Hello ,

I have multiple ranges for each name in EMPLOYEE sheet (about 10000rows for multiple ranges and multiple names) .
in EMPLOYEE sheet contains specific cells (NAME,FIRST BALANCE,SALARY,NET AMOUNT) should convert to headers consecutively when run the form when select sheet name from combobox1 and sum the columns (FIRST BALANCE,SALARY,NET AMOUNT) by show TOTAL row in lastrow in listbox without forget adding ITEM column in first column to autonumbering 1,2,....

OMRAN.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-20000
3			SALARY	3000
4			NET AMOUNT	-17000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	-2000
9			SALARY	3000
10			NET AMOUNT	1000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1000
15			SALARY	2500
16			NET AMOUNT	3500
EMPLOYEE

result

thanks

 

[djclements]

Based on your sample data and description, try the following code:

Private Sub ComboBox1_Change()

'Clear the previous listbox results and add the header row
    With Me.ListBox1
        .Clear
        .AddItem
        .List(0, 0) = "ITEM"
        .List(0, 1) = "NAME"
        .List(0, 2) = "FIRST BALANCE"
        .List(0, 3) = "SALARY"
        .List(0, 4) = "NET AMOUNT"
    End With

'Set the source worksheet, if it exists
    On Error Resume Next
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(Me.ComboBox1.Text)
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo ErrorHandler

'Load the source data into an array
    Dim lastRow As Long, arr As Variant
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    arr = ws.Cells(1, 3).Resize(lastRow, 2).Value

'Loop thru the array and populate the listbox with each new item found
    Dim i As Long, lstId As Long, tot1 As Double, tot2 As Double, tot3 As Double
    lstId = 1
    For i = 1 To lastRow
        If arr(i, 1) = "NAME" Then
            With Me.ListBox1
                .AddItem
                .List(lstId, 0) = lstId
                .List(lstId, 1) = Format(arr(i + 1, 1), "#,##0.00")
                .List(lstId, 2) = Format(arr(i + 1, 2), "#,##0.00")
                .List(lstId, 3) = Format(arr(i + 2, 2), "#,##0.00")
                .List(lstId, 4) = Format(arr(i + 3, 2), "#,##0.00")
            End With
            tot1 = tot1 + arr(i + 1, 2)
            tot2 = tot2 + arr(i + 2, 2)
            tot3 = tot3 + arr(i + 3, 2)
            lstId = lstId + 1
        End If
    Next i

'Add the total row to the listbox
    With Me.ListBox1
        .AddItem
        .List(lstId, 0) = "TOTAL"
        .List(lstId, 2) = Format(tot1, "#,##0.00")
        .List(lstId, 3) = Format(tot2, "#,##0.00")
        .List(lstId, 4) = Format(tot3, "#,##0.00")
    End With

Exit Sub

ErrorHandler:
    If Err.Number = 94 Then
    ' Invalid use of Null
        Err.Clear
        Resume Next
    Else
        MsgBox Err.Description, vbExclamation, "Runtime Error: " & Err.Number
    End If

End Sub

 

[Omran Y]

Awesome!
many thanks.