convert code to search data horizontally and show data vertically

[Mussa]

hi
I have this code works with vertical data in sheet and show in listbox based on search into textbox1 based on column D . now I want change the code based on horizontal data in sheet but show the data should be vertical in listbox based on search into textbox based on first row contain CODE as in A1 I take the example in sheet example how should show the data in listbox on userform when search for CCS-1

orginal data

VR.xlsm
	A	B	C	D	E
1	CODE	CCS-1	CCS-2	CCS-3	CCS-4
2	CLIENT NO	CC2-TRB-1	CC2-TRB-2	CC2-TRB-3	CC2-TRB-4
3	INVOICE NO	INV-TRGG-1	INV-TRGG-2	INV-TRGG-3	INV-TRGG-4
4	ID-CC	IDTR-100	IDTR-101	IDTR-102	IDTR-103
5	BB-R	FFDOOD-1	FFDOOD-2	FFDOOD-3	FFDOOD-4
6	TT-Y	PACK-2	PACK-3	PACK-4	PACK-5
7	OR-GG	TTR	BBR	LLR	MMR
8	QTY	60.00	10	5	10
9	PRICE	$120.00	$130.00	$100.00	$289.00
10	TOTAL	$7,200.00	$1,300.00	$500.00	$2,890.00
PURCHASE

this is example should be show in listbox when search

VR.xlsm
	A	B	C	D	E	F	G	H	I	J
1	CODE	CLIENT NO	INVOICE NO	ID-CC	BB-R	TT-Y	OR-GG	QTY	PRICE	TOTAL
2	CCS-1	CC2-TRB-1	INV-TRGG-1	IDTR-100	FFDOOD-1	PACK-2	TTR	60.00	$120.00	$7,200.00
EXAMPLE

Option Explicit

Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Private Sub TextBox2_Change()
  Call FilterData
End Sub

Private Sub TextBox3_Change()
  Call FilterData
End Sub

Sub FilterData()
    Dim i As Long, ii As Long, n As Long
    Me.ListBox1.List = a
    If Me.TextBox1 = "" Then Exit Sub
    With Me.ListBox1
        .Clear
        For i = 0 To UBound(a, 1)
            If UCase$(a(i, 3)) Like UCase$(Me.TextBox1) & "*" Then
                .AddItem
                .List(n, 0) = n + 1
                For ii = 1 To UBound(a, 2)
                    .List(n, ii) = a(i, ii)
                Next
                n = n + 1
            End If
        Next
    End With
End Sub
Private Sub UserForm_Initialize()
    Dim lindex&
    Dim rngDB As Range, rng As Range
    Dim i, myFormat(1) As String
    Dim sWidth As String
    Dim vR() As Variant
    Dim n As Integer
    Dim myMax As Single
    Set rngDB = Range("A2:E20")
    For Each rng In rngDB
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng
    myMax = WorksheetFunction.Max(vR)
    For i = 1 To n
        vR(i) = myMax
    Next i
    With Sheets("purchase").Cells(1).CurrentRegion
        myFormat(0) = .Cells(2, 8).NumberFormatLocal
        myFormat(1) = .Cells(2, 9).NumberFormatLocal
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        a = .Cells(1).CurrentRegion.Value
    End With

    sWidth = Join(vR, ";")
    Debug.Print sWidth
    With ListBox1
        .ColumnCount = 10
        .ColumnWidths = sWidth '<~~ 63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63
        .List = rng.Value
        .BorderStyle = fmBorderStyleSingle
        For lindex = 0 To .ListCount - 1
            '.List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mm/yyyy"))   ' BL = dates
            .List(lindex, 0) = lindex + 1
            .List(lindex, 7) = Format$(.List(lindex, 7), myFormat(0))
            .List(lindex, 8) = Format$(.List(lindex, 8), myFormat(1))
            .List(lindex, 9) = Format$(.List(lindex, 9), myFormat(1))
        Next
        a = .List  '<--- this line
    End With
End Sub

 

[alansidman]

An alternative is to transpose your data with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

[Mussa]

thanks but I would do that directly in listbox . not depends on sheet . just to understand how should show data in listbox

 

[Mussa]

can do that directly in listbox experts?