select range from combobox to populate data in listbox

[abdo meghari]

Hi
this code for DanteAmore and I modified to fit with my requirements but I faild
my goal select the header from combobox1 by matching with header in column G then should populate data in the listbox for the range is relating with the header in columns (F:I) inside the sheet. .

Option Explicit
Dim a As Variant

Sub FilterData()
  Dim txt1 As String
  Dim I As Long, j As Long, k As Long
  
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For I = 1 To UBound(a)
    If ComboBox1.Value = "" Then txt1 = a(I, 2) Else txt1 = ComboBox1.Value
    
    If LCase(a(I, 2)) Like LCase(txt1) & "*" Then
      k = k + 1
      For j = 1 To 4
        b(k, j) = a(I, j)
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub ComboBox1_Change()
 Call FilterData
End Sub

Private Sub UserForm_Activate()
  a = Sheets("RANGES").Range("F2:I" & Sheets("RANGES").Range("G" & Rows.Count).End(3).Row).Value
End Sub

data in sheet

RANGES.xlsm
	A	B	C	D	E	F	G	H	I
1		SR					SR
2	ITEM	DATE	TYPE	TOTAL		ITEM	DATE	NOT PAID	PAID
3	1	10/06/2023	NOT PAID	7,720.00		1	10/06/2023	9,720.00	10,810.00
4	2	10/06/2023	NOT PAID	2,000.00		2	11/06/2023	7,000.00	0.00
5	3	10/06/2023	PAID	6,810.00		SUM		16,720.00	10,810.00
6	4	10/06/2023	PAID	4,000.00
7	5	11/06/2023	NOT PAID	7,000.00			SVR
8	SUM			27,530.00		ITEM	DATE	NOT PAID	PAID
9						1	10/06/2023	5,720.00	14,040.00
10		SVR				2	11/06/2023	13,230.00	0.00
11	ITEM	DATE	TYPE	TOTAL		3	12/06/2023	0.00	15,000.00
12	1	10/06/2023	NOT PAID	5,720.00		SUM		18,950.00	29,040.00
13	2	10/06/2023	PAID	14,040.00
14	3	11/06/2023	NOT PAID	1,230.00			SDE
15	4	11/06/2023	NOT PAID	12,000.00		ITEM	DATE	NOT PAID	PAID
16	5	12/06/2023	PAID	14,000.00		1	10/06/2023	0.00	5,000.00
17	6	12/06/2023	PAID	1,000.00		2	11/06/2023	2,200.00	2,000.00
18	SUM			47,990.00		3	12/06/2023	3,000.00	0.00
19						SUM		5,200.00	7,000.00
20
21		SDE					FGR
22	ITEM	DATE	TYPE	TOTAL		ITEM	DATE	NOT PAID	PAID
23	1	10/06/2023	PAID	4,000.00		1	10/06/2023	0.00	2,500.00
24	2	10/06/2023	PAID	1,000.00		2	11/06/2023	1,100.00	0.00
25	3	11/06/2023	PAID	2,000.00		3	12/06/2023	9,200.00	0.00
26	4	11/06/2023	NOT PAID	2,200.00		SUM		10,300.00	2,500.00
27	5	12/06/2023	NOT PAID	3,000.00
28	SUM			12,200.00
29
30
31		FGR
32	ITEM	DATE	TYPE	TOTAL
33	1	10/06/2023	PAID	1,200.00
34	2	10/06/2023	PAID	1,300.00
35	3	11/06/2023	NOT PAID	1,100.00
36	4	12/06/2023	NOT PAID	2,200.00
37	5	12/06/2023	NOT PAID	7,000.00
38	SUM			12,800.00
39
RANGES

Cell Formulas
Range	Formula
D8,D38,D28	D8	=SUM(D3:D7)
D18	D18	=SUM(D12:D17)

here example when select the header in combobox1

 

[DanteAmor]

Hi Abdo:

Check if this is what you want:

Replace all your code for this:

Option Explicit

Sub FilterData()
  Dim sh As Worksheet
  Dim f As Range, ar As Range
  Dim i As Long, j As Long, n As Long
  Dim col As Variant
  
  Set sh = Sheets("RANGES")
  ListBox1.Clear
  For Each col In Array("B:B", "G:G")
    Set f = sh.Range(col).Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      For i = f.Row + 1 To sh.Range("A" & Rows.Count).End(3).Row
      
        With ListBox1
          .AddItem
          .List(n, 0) = sh.Cells(i, f.Column - 1 + 0).Value
          .List(n, 1) = Format(sh.Cells(i, f.Column - 1 + 1).Value, "dd/mm/yyyy")
          .List(n, 2) = sh.Cells(i, f.Column - 1 + 2).Value
          .List(n, 3) = Format(sh.Cells(i, f.Column - 1 + 3).Value, "#,##0.00")
        End With
        
        n = n + 1
        If sh.Cells(i, f.Column - 1).Value = "SUM" Then
          Exit For
        End If
      Next
    End If
  Next
End Sub

Private Sub ComboBox1_Change()
 Call FilterData
End Sub

Note: You should have previously loaded the combobox.


I hope to hear from you
Dante Amor

 

[abdo meghari]

perfect !
just I would for column G:G , I don't need column B:B
I try to change this

  For Each col In Array("B:B", "G:G")

to

  For Each col In range("G:G")

and from

 Dim col As Variant

to

 Dim col As Range

but shows error Method range of object worksheet failed in this line

Set f = sh.Range(col).Find(ComboBox1.Value, , xlValues, xlWhole, , , False)

may you help me ,please?

 

[DanteAmor]

Try:

Sub FilterData()
  Dim sh As Worksheet
  Dim f As Range, ar As Range
  Dim i As Long, j As Long, n As Long
  Dim col As Variant
  
  Set sh = Sheets("RANGES")
  ListBox1.Clear
    Set f = sh.Range("B:B").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      For i = f.Row + 1 To sh.Range("A" & Rows.Count).End(3).Row
      
        With ListBox1
          .AddItem
          .List(n, 0) = sh.Cells(i, f.Column - 1 + 0).Value
          .List(n, 1) = Format(sh.Cells(i, f.Column - 1 + 1).Value, "dd/mm/yyyy")
          .List(n, 2) = sh.Cells(i, f.Column - 1 + 2).Value
          .List(n, 3) = Format(sh.Cells(i, f.Column - 1 + 3).Value, "#,##0.00")
        End With
        
        n = n + 1
        If sh.Cells(i, f.Column - 1).Value = "SUM" Then
          Exit For
        End If
      Next
    End If
End Sub

Private Sub ComboBox1_Change()
 Call FilterData
End Sub

 

[abdo meghari]

just I would for column G:G , I don't need column B:B

I changed to

    Set f = sh.Range("G:G").Find(ComboBox1.Value, , xlValues, xlWhole, , , False)

now works greatly , thank you so much .