mismatch error when using sheets in array to populate data on userform

[Mussala]

Hello ,
I try to populate data for all of sheets in listbox on userform , but shows mismatch error in this line

r = Sheets(sh).Range("A1").CurrentRegion.Value

Dim r As Variant


Sub LoadData()
Dim sh As Variant
For Each sh In Sheets(Array("Mussala", "mssau", "mjhgsg"))
r = Sheets(sh).Range("A1").CurrentRegion.Value
Next sh
 With Me.ListBox1
        .List = r
End With

End Sub
Private Sub UserForm_Initialize()
Call LoadData
  
End Sub

I hope some body fix this problem .

 

[Artik]

Try:

    For Each sh In Array("Mussala","mssau","mjhgsg")
        r = Sheets(sh).Range("A1").CurrentRegion.Value
    Next sh

Artik

 

[Mussala]

thanks,
but unfortunately it just show Mussala sheet and ignore the others !
should show all of data for all of sheets.

 

[Mussala]

Can any body help,please?

 

[Fuji]

Sub LoadData()
    Dim s$(1), x
    s(0) = "Select * From `Mussala$` Union All Select * From `mssau$` Union All Select * From `mjhgsg$`;"
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        x = .GetRows
    End With
    With Me.ListBox1
        .ColumnCount = UBound(x, 1) + 1
        .Column = x
    End With
End Sub

 

[Mussala]

Awesome !
just I want getting rid of specific rows from showing in listbox . when there is OPENING word in column C and TOTAL word in column A , then shouldn't show .
sorry , your code is difficult to me and I can't modify it .
thanks again.

 

[Fuji]

Since you don't show us the data lyouts, my guess

    s(0) = "Select * From `Mussala$A2:G` Where F1 <> 'total' " & _
           "Union All (Select * From `mssau$A2:G` Where F1 <> 'total') " & _
           "Union All (Select * From `mjhgsg$A2:G` Where F1 <> 'total');"
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=No';"

 

[Mussala]

it shows me Run Time Error'-2147217913 (80040e07)' __Data type mismatch in criteria expression

.Open s(0), s(1), 3, 3, 1

Since you don't show us the data lyouts, my guess

mu1.xlsm
	A	B	C	D	E	F
1	DATE	INVOICE NO	TYPE	DEBIT	CREDIT	BALANCE
2	31/12/2023		OPENING	2,000.00		2,000.00
3	01/01/2024	AL0001	CASH SALES		1,000.00	1,000.00
4	02/01/2024	AL0002	FORWARD SALES	2,000.00		3,000.00
5	03/01/2024	AL0003	CASH SALES		1,000.00	2,000.00
6	04/01/2024	AL0004	FORWARD SALES	2,000.00		4,000.00
7	04/01/2024	AL0005	FORWARD BUYING		2,000.00	2,000.00
8	05/01/2024	AL0006	FORWARD BUYING		1,000.00	1,000.00
9	06/01/2024	VFRT 2000	CASH ACCRUING	2,000.00		3,000.00
10	07/01/2024	VFRT 2001	PAID ACCRUING		1,000.00	2,000.00
11	07/01/2024	VFRT 2002	CASH ACCRUING	1,500.00		3,500.00
12	08/01/2024	VFRT 2003	PAID ACCRUING		1,000.00	2,500.00
13	09/01/2024	FRT0001	FORWARD BUYING RETURNS	2,000.00		4,500.00
14	10/01/2024	FRT0002	FORWARD BUYING RETURNS	1,000.00		5,500.00
15	11/01/2024	FRT0003	FORWARD SALES RETURNS		1,000.00	4,500.00
16	12/01/2024	FRT0004	FORWARD BUYING RETURNS		500.00	4,000.00
17	13/01/2024	CCS0001	CASH BUYING	1,000.00		5,000.00
18	13/01/2024	ZXS1000	CASH BUYING RETURNS	1,000.00		6,000.00
19	13/01/2024	ZXS1001	CASH BUYING RETURNS	2,000.00		4,000.00
20	14/01/2024	ZXS1002	CASH SALES RETURNS		2,000.00	2,000.00
21	TOTAL			16,500.00	10,500.00	6,000.00
Mussala

Cell Formulas
Range	Formula
D21:E21	D21	=SUM(D2:D20)
F21	F21	=D21-E21

mu1.xlsm
	A	B	C	D	E	F
1	DATE	INVOICE NO	TYPE	DEBIT	CREDIT	BALANCE
2	31/12/2023		OPENING		5,000.00	-5,000.00
3	01/01/2024	AL0007	FORWARD SALES	6,000.00		1,000.00
4	02/01/2024	AL0008	FORWARD SALES	4,000.00		5,000.00
5	03/01/2024	AL0009	CASH SALES		1,000.00	4,000.00
6	04/01/2024	AL0010	CASH SALES		500.00	3,500.00
7	04/01/2024	AL0011	FORWARD BUYING		2,000.00	2,000.00
8	05/01/2024	AL0012	PAID ACCRUING		1,000.00	1,000.00
9	05/01/2024	AL0013	PAID ACCRUING		500.00	500.00
10	05/01/2024	AL0014	PAID ACCRUING		500.00	0.00
11	08/01/2024	AL0015	FORWARD BUYING		500.00	-500.00
12	09/01/2024	AL0016	FORWARD BUYING		500.00	-1,000.00
13	10/01/2024	FRT0005	FORWARD BUYING RETURNS	2,000.00		1,000.00
14	10/01/2024	FRT0006	FORWARD BUYING RETURNS	2,000.00		3,000.00
15	11/01/2024	ZXS1003	CASH BUYING RETURNS	1,000.00		4,000.00
16	12/01/2024	ZXS1004	CASH SALES RETURNS		1000	3,000.00
17	TOTAL			15,000.00	12,500.00	2,500.00
mssau

Cell Formulas
Range	Formula
D17:E17	D17	=SUM(D2:D16)
F17	F17	=D17-E17

mu1.xlsm
	A	B	C	D	E	F
1	DATE	INVOICE NO	TYPE	DEBIT	CREDIT	BALANCE
2	01/01/2024	AL00017	FORWARD SALES	3,000.00		3,000.00
3	02/01/2024	AL00018	FORWARD SALES	4,000.00		7,000.00
4	03/01/2024	AL00019	CASH SALES		1,000.00	4,000.00
5	04/01/2024	AL00020	CASH SALES		500.00	3,500.00
6	04/01/2024	AL00021	FORWARD BUYING		2,000.00	2,000.00
7	05/01/2024	VFRT 2003	CASH ACCRUING	5,000.00		7,000.00
8	06/01/2024	AL00023	FORWARD BUYING		4,000.00	3,000.00
9	07/01/2024	VFRT 2004	CASH ACCRUING	1,000.00		4,000.00
10	08/01/2024	AL00025	FORWARD BUYING		5,000.00	-1,000.00
11	09/01/2024	VFRT 2005	CASH ACCRUING	3,000.00		-2,000.00
12	10/01/2024	FRT0006	FORWARD SALES RETURNS		1,000.00	-3,000.00
13	11/01/2024	FRT0007	FORWARD SALES RETURNS		1,000.00	-4,000.00
14	12/01/2024	CCS0002	CASH BUYING	1,000.00		-3,000.00
15	13/01/2024	CCS0003	CASH BUYING	3,000.00		0.00
16	TOTAL			20,000.00	14,500.00	5,500.00
mjhgsg

Cell Formulas
Range	Formula
D16:E16	D16	=SUM(D2:D15)
F16	F16	=D16-E16

so when show data in listbox will exclude the last row for each sheet contains TOTAL word in column A also exclude row if there is OPENING word is existed in column C
not all of sheets will contain OPENING word, but always contains TOTAL word in lastrow.

 

[Fuji]

Sub LoadData()
    Dim s$(1), x, e
    For Each e In Array("Mussala", "mssau", "mjhgsg")
        s(0) = s(0) & IIf(s(0) = "", "", "Union All (") & "Select " & _
        "Format(`DATE`,'dd/mm/yyyy'), `INVOICE NO`, `TYPE`, `DEBIT`, `CREDIT`, " & _
        "`BALANCE` From `" & e & "$` Where `TYPE` Is Not Null " & IIf(s(0) = "", "", ") ")
    Next
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        x = .GetRows
    End With
    With Me.ListBox1
        .ColumnCount = UBound(x, 1) + 1
        .Column = x
    End With
End Sub

 

[Mussala]

thanks ,
also I would exclude row contains OPENING word in column C as I said

also exclude row if there is OPENING word is existed in column C

when there is OPENING word in column C and TOTAL word in column A , then shouldn't show .