返回列表 上一主題 發帖

[發問] 雙迴圈或多迴圈 寫法

singo1232001
　暱稱: 熱狗慶

中學生Rank: 2

帖子
353 
主題
5 
精華
0 
積分
386 
點名
0  
作業系統
windows7 
軟體版本
vba,vb,excel2007 
閱讀權限
20 
性別
男 
註冊時間
2017-1-8 
最後登錄
2024-5-4 

論壇榮譽勳章論壇贊助勳章
1# 跳轉到 »
發表於 2023-10-6 15:47 | 顯示全部帖子
回復 3# fantersy


   

我求出來的答案不同

Sub test()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("訂單未交")
s.[G:AZ].ClearContents

q = ""
q = q & "select 產品編號,(數量 - 總出貨數量) from [axmr450$A1:T] where 產品編號 in( "
q = q & "select 料件編號 from [庫存$A1:G] where 倉庫編號 like '%JMZ1%' "
q = q & " )"
Set RS = CN.Execute(q)
s.[G3].CopyFromRecordset RS

q = ""
q = q & "select 產品編號,sum(dif) from ("
q = q & "select 產品編號,(數量 - 總出貨數量) as dif from [axmr450$A1:T] where 產品編號 in( "
q = q & "select 料件編號 from [庫存$A1:G] where 倉庫編號 like '%JMZ1%' "
q = q & " ) "
q = q & " ) group by 產品編號"
Set RS = CN.Execute(q)
s.[J3].CopyFromRecordset RS

q = ""
q = q & "select t3.料號,t3.料號數總和 from  ( "
q = q & "select * from [訂單未交$B2:B] as t1 left join ( "
q = q & "select 產品編號,sum(dif) as 料號數總和 from ( "
q = q & "select 產品編號,(數量 - 總出貨數量) as dif from [axmr450$A1:T] where 產品編號 in( "
q = q & "select 料件編號 from [庫存$A1:G] where 倉庫編號 like '%JMZ1%' "
q = q & " ) "
q = q & " ) group by 產品編號 "
q = q & " ) as t2 on t1.料號 = t2.產品編號 "
q = q & " ) as t3"
Set RS = CN.Execute(q)
s.[M3].CopyFromRecordset RS

s.[G2:N2] = Array("axmr所有", "逐項差數", "", "合併", "未交總數", "", "對照未交", "未交數")
End Sub

訂單未交計算.zip (798.13 KB)

TOP

singo1232001
　暱稱: 熱狗慶

中學生Rank: 2

帖子
353 
主題
5 
精華
0 
積分
386 
點名
0  
作業系統
windows7 
軟體版本
vba,vb,excel2007 
閱讀權限
20 
性別
男 
註冊時間
2017-1-8 
最後登錄
2024-5-4 

論壇榮譽勳章論壇贊助勳章
2#
發表於 2023-10-7 23:27 | 顯示全部帖子
回復 4# singo1232001


避免過於程序過長精簡
Sub test2精簡版()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("訂單未交"): s.[C3:C9999].ClearContents
q = "select t2.料數和 from [訂單未交$B2:B] as t1 left join ( "
q = q & "select 產品編號,IIf(IsNull(sum(dif)), 0, sum(dif)) as 料數和 from ( "
q = q & "select 產品編號,(數量 - 總出貨數量) as dif from [axmr450$A1:T] where 產品編號 in( "
q = q & "select 料件編號 from [庫存$A1:G] where 倉庫編號 like '%" & s.[c1] & "%' "
q = q & " ) "
q = q & " ) group by 產品編號 "
q = q & " ) as t2 on t1.料號 = t2.產品編號 "
s.[c3].CopyFromRecordset CN.Execute(q)
End Sub

------------------------------------------------------
Sub test3逐步推進精簡版()
Set CN = CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
CN.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("訂單未交"): s.[G:AZ].ClearContents

    q = "select 產品編號,(數量 - 總出貨數量) as dif from [axmr450$A1:T] where 產品編號 in( "
q = q & "select 料件編號 from [庫存$A1:G] where 倉庫編號 like '%" & s.[c1] & "%' "
p = q & " ) "
s.[G3].CopyFromRecordset CN.Execute(p)

    q = "select 產品編號,IIf(IsNull(sum(dif)), 0, sum(dif)) as 料數和 from ( " & p
p = q & " ) group by 產品編號 "
s.[J3].CopyFromRecordset CN.Execute(p)

    q = "select t1.料號,t2.料數和  from [訂單未交$B2:B] as t1 left join ( " & p
p = q & " ) as t2 on t1.料號 = t2.產品編號 "
s.[M3].CopyFromRecordset CN.Execute(p)

s.[G2:N2] = Array("axmr所有", "逐項差數", "", "合併", "未交總數", "", "對照未交", "未交數")
End Sub


可在C1輸入查詢編號
訂單未交計算 v1.zip (802.92 KB)

TOP

        靜思自在 : 每天無所事事，是人生的消費者，積極、有用才是人生的創造者。
返回列表 上一主題