麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 跨工作表並指定儲存格在往下迴圈查詢 [打印本頁]

作者: BV7BW 時間: 2020-4-30 08:42 標題: 跨工作表並指定儲存格在往下迴圈查詢

問題1: 工作表1(A,B,C,D,E,F)欄中,由數量(D)欄內儲存格判斷依據帶出資料轉換到工作表3(A,B,C,D,E,F)欄,(例如工作表3)依序迴圈往下排列
其工作表1中名稱(A)欄是下拉清單,編號(B)欄,商品(C)欄,價格(E)欄是固定資料,數量(D)欄是人工填入,總價(F)欄是(D2)*(E2)
問題2: 如轉換到工作表2可指定儲存格?當工作表1數量(D)欄由人工填入後用巨集按扭轉換到工作表2
例如:工作表1(A2)轉工作表2(B2),工作表1(B2)轉工作表2(B6),工作表1(C2)轉工作表2(C6),
工作表1(D2)轉工作表2(D6),工作表1(E2)轉工作表2(E6),工作表1(F2)轉工作表2(F6),
其中工作表1(A2)只帶出1次,其餘(B2:B12,C2:C12,D2:D12,E2:E12,F2:F12)依序迴圈往下排列
問題1與問題2: 皆因是由數量(D)欄內儲存格中作為判斷依據帶出編號(B)欄,商品(C)欄,價格(E)欄,總價(F)欄,所以數量(D)欄是不確定哪列儲存格有數字
當數量(D)欄某一列儲存格有數字出現(或變化異動),也就是數量(D)欄某一列中出現0.1到99.9(或是有變化異動)時既可觸發帶出(B,C,D,E,F)欄
沒出現數字(或沒變化異動)的儲存格就不用帶出再繼續迴圈往下查詢至最後一列

作者: 准提部林 時間: 2020-4-30 11:04

Sub 轉出資料()
Dim R&, xR As Range, xE As Range, N&
If [工作表1!A2] = "" Then MsgBox "**尚未填入名稱! ": Exit Sub
R = [工作表1!B65536].End(xlUp).Row - 1
If Application.Sum([工作表1!D2].Resize(R)) = 0 Then
MsgBox "**尚未填入數量! ": Exit Sub
End If
[工作表2!B6:F200].ClearContents
[工作表2!B2] = [工作表1!A2]
For Each xR In [工作表1!B2].Resize(R)
If Val(xR(1, 3)) = 0 Then GoTo 101
N = N + 1
[工作表2!B5:F5].Offset(N, 0) = xR.Resize(, 5).Value
'----------------------------
Set xE = [工作表3!D65536].End(xlUp)(2, -1)
If N = 1 Then xE(1, 0) = [工作表1!A2]
xE.Resize(, 5) = xR.Resize(, 5).Value
101: Next
With [工作表1!D2].Resize(R)
.Copy [工作表1!H2] '數量貼至備存區, 以供參考
.ClearContents '清空數量, 待下次重新輸入
End With
[工作表1!A2] = "" '清空名稱, 待下次重新輸入
[工作表1!i2] = Date: [工作表1!i3] = Time '記錄轉出日期時間
End Sub

複製代碼

[attach]31981[/attach]

'=========================

作者: BV7BW 時間: 2020-4-30 12:06

謝謝准大
這些正是我想要做法.我用2個月時間網路練習作法想不到准大在幾小時內解析出,真是高手,高手,高高手謝謝你
因我是(60)進入Excel2個多月,請容我一一分解.真是謝謝你

作者: BV7BW 時間: 2020-5-7 00:30

回復 2# 准提部林

准大先進老師及各位先進前輩,不計另嗇多方指導"謝謝"
如上題有多方面理解獲益匪淺,但只理解皮毛.未深入原理.懇請再容多加努力.努力學習探討,(例如Dim R&, xR As Range, xE As Range, N&這些出至哪裡.函意為何
今如上題中再新增項目.請再賜教"謝謝"

作者: BV7BW 時間: 2020-5-7 15:03

回復 4# BV7BW

抱歉.上傳資料有誤.再補船1次抱歉

作者: 准提部林 時間: 2020-5-8 09:48

回復 5# BV7BW

類似"進/銷貨"單吧!
進銷存>牽涉到[新增-查詢-更改-刪除]問題,
這費時耗力, 實無法這樣逐步跟帖,
可自行先網搜找看看有沒現成的檔案範例或程式碼參考!!!

Dim ~~~~ 只是定義[變數], 可看vbe說明檔, 或G搜關鍵字__excel vba 變數

作者: BV7BW 時間: 2020-5-8 10:24

回復 6# 准提部林

感謝准大先進.指導
我在朝這方面努力探討.謝謝你
如有不便之處.望請諒解...謝謝

作者: BV7BW 時間: 2020-5-15 02:14

回復 6# 准提部林

修改後版本之增列部分
   [工作表6!G2] = [工作表1!G2]
   [工作表6!H2] = [工作表1!H2]
   [工作表6!I2] = [工作表1!I2]
   [工作表6!J2] = Date '記錄轉出日期時間
[工作表1!I2] = Range("I2") + 1
Copy [工作表1!J2] '數量貼至備存區, 以供參考
不知從哪修改為往下排列
望請准大勞心指教
如蒙賜教,實感德惠

作者: 准提部林 時間: 2020-5-15 10:48

回復 8# BV7BW

看不懂意圖~~
而且進銷單應不可能是如此簡單的格式,
商品項目若很多, 這種格式就不夠使用,
若再考慮後續的統計(例如:月結請款), 資料表也不能這樣設計!!!
================================
也許你可能只是想學習VBA, 但一下就找這種題目, 教個一年半載是跑不掉的,
還是建議去找找現成的帖子看看, 最好也去買VBA基礎書籍,
把基本功打好, 至少得能看懂別人寫的程式碼!

作者: 准提部林 時間: 2020-5-15 14:19

這是個簡易法, 只有存入, 沒有更改及刪除,
雖是簡單做法, 還是要花大半天寫程式及設計表格:
[attach]32021[/attach]

等完全看得懂裡面的程式碼, 有問題再說~~

作者: BV7BW 時間: 2020-5-15 15:55

回復 10# 准提部林

謝謝你.我會加以理解真是感謝你

作者: 准提部林 時間: 2020-5-15 21:00

回復 11# BV7BW

看這個表的"品項"約有近800樣(後續可能新增),
光要輸入出貨單(品項應是各種類的混合), 這就大費周章,
整個檔案要完成, 不是短時間!

作者: 准提部林 時間: 2020-5-16 21:02

本帖最後由准提部林於 2020-5-16 21:03 編輯

花了一整天完成此檔, 試看看:
[attach]32036[/attach]

進貨部份, 可將檔案複製一份, 修改成所需即可~~
太累, 只能幫到這!!!

檔案可"另存新檔" 為 xlsm, 即可在2007以上使用

作者: BV7BW 時間: 2020-5-16 21:59

回復 13# 准提部林

謝謝准大幫忙..辛苦了真感心

作者: 准提部林 時間: 2020-5-17 13:09

本帖最後由准提部林於 2020-5-18 11:58 編輯

回復 14# BV7BW

再整修一番:
[attach]32038[/attach]

1) 利用定義名稱, 將重要的儲存格定位, 以利往後若有變動時, 較好維謢
2) 客戶表, 應以[編號]為主索引, 是唯一性不可變動, 所以移至最左欄, 名稱則可修改(客戶名稱可能會變動)
3) [出貨單]改由程式控制, 列數依資料自行調整
4) [商品項目]的[編號], 建議由1000開始, 避免以0為開頭, 若能像客戶編號以英文開頭最好,
可依各種類分別賦于A~Z為首碼(但應避免使用O,I,Q,J,M,W)
__O/Q像0(零) I像1, J/M/W字窄字寬與其它字元看起來不對齊(除非設為-細明體)

作者: BV7BW 時間: 2020-5-17 15:13

回復 15# 准提部林

再次謝謝准大勞心勞德解惑
我正演練操作.再一一跟你匯報.謝謝.感恩

作者: BV7BW 時間: 2020-5-19 18:50

回復 16# BV7BW

抱歉準大演練時不小心把列表機程式弄亂了自己修很多天才在向你報告
現有要改的再工作表6與4部分

作者: 准提部林 時間: 2020-5-21 21:00

回復 17# BV7BW

1) 採購員核對資料表
2) 分發至菜商採購表
這兩表看起來是一樣的, 只是採購多了空白行去隔開 ???

開于提及第1版你較好理解的問題, 因那對我來說只是草稿性質,
它也只提供輸入及轉存的功能, 當然以最簡單的程式即可解決!

後來陸續追加的問題及需求, 為了減少人工輸入作業(尤其是商品分類分區建立, 在選單上有其困難度),
也為了讓使用者, 僅憑表上的按鈕即可操作, 所以程式會複雜化(複雜化的程式是為了達到操作簡易),
任何生手只要稍教幾次, 都可上手!

因此, 會與你的需求有所違差!

'=================================

作者: 准提部林 時間: 2020-5-21 21:11

回復 17# BV7BW

3) 給司機核對各客戶訂貨上籃及上車用(客戶配貨表)
一般送貨員不是都用"送貨單"的內容去撿貨裝箱嗎??? 不然怎麼簽收及對帳請款???
要將各品項+客戶編號及名稱, 這就變成二維表, 用樞紐分析表即可達成, 但應不合你的需求,
但你只提供草稿, 並未建置表格樣式, 實在難辦, 以下我做三個樣板:
[attach]32061[/attach]

[attach]32062[/attach]

[attach]32063[/attach]

這三個都不好弄, 若客戶名稱太長或長短不一, 整張就很亂~~~
參考即可!

實在挪不出時間寫這些, 或者是我將你的需求想複雜了,
也許請其他大大幫忙, 說不定可做到更合適的檔案!!!

===========================================

作者: BV7BW 時間: 2020-5-23 18:19

回復 19# 准提部林

抱歉準大老師.我的愚蠢.讓你多白費心真抱歉
看到準大回復想到老師在第2版出來就已經可行了
剛剛想到工作表6與4是不用格式化只要能印出內容就可.也不用裱框只要文字
因為我想到工作表4是複印出來

作者: 准提部林 時間: 2020-5-23 19:08

回復 20# BV7BW

你要加客戶編號及名稱, 要怎麼表現???

作者: BV7BW 時間: 2020-5-23 21:46

回復 21# 准提部林

準大老師
工作表6(出貨明細)重於客戶名稱.客戶編號.類別.項目編號.商品.數量其餘可無可有
工作表4(採購明細)重於類別.項目編號.商品.數量.其餘可無.所以客戶名稱及編號不用呈現出
這是第2版之出貨明細(工作表6)才有這樣資料
第1版之出貨明細(工作表6)沒又這樣資料也是我一直要更改地方

作者: BV7BW 時間: 2020-5-23 21:58

回復 22# BV7BW

至於項目編號我改為A0001到Z0001已改好
其中準大老師說避免用到幾個字母
我想他是編號在實際上可分辨出.或者在00001中在0字母上加上顏色既可分辨

作者: BV7BW 時間: 2020-5-23 22:20

回復 23# BV7BW

這裡有第1版本樣式
其中工作表6.工作表4之版本

作者: BV7BW 時間: 2020-5-23 22:21

回復 23# BV7BW

這裡有第1版本樣式
其中工作表6.工作表4之版本

作者: 准提部林 時間: 2020-5-23 22:24

本帖最後由准提部林於 2020-5-23 22:29 編輯

回復 20# BV7BW

花了半天, 玩看看吧!
[attach]32073[/attach]

[出貨輸入]右方的項目, 只預設最多80樣商品,
所以, 各大分類下的項目, 不要超過此數, 若超過最好再細分,
例如: 蔬菜類, 可再細分為
[葉菜類]
[球菜類]-高麗菜,花椰菜,大白菜.....
[莖根瓜類]太多種, 可分 [莖根類][瓜類]
因視窗版面有點寬, 最好用16:9螢幕, 避免滑鼠滑來滑去~~

檔案中有些[定義名稱],
多看幾次程式碼應可慢慢看懂, 其它都是最基本的公式/ARRAY/字典檔的運用而已,
大概就這樣!
操作的人, 幾乎不用打字了

========================================

作者: 准提部林 時間: 2020-5-24 11:20

本帖最後由准提部林於 2020-5-24 11:39 編輯

再修一版:
[attach]32076[/attach]

1) "日期" 改以民國日期為顯示格式
2) "單號" 改以民國年, 例如:1090524001
3) 僅供參考用 : "品項"編號, 每類最多80項, 各大類以其屬性相同者, 共用同一英文首碼,
例如:
蔬菜類/包菜類/瓜果類/莖根類/豆筴芽類--同為原生農產品(未加工), 以 A101~A501 賦于編號
辛香類/菇類--同為原生農產品(調香及配料), 編B101~B201
食材加工類/加工食品類/豆製品類/魚丸類/醃製類--都屬加"人工"製程產品, 編E101~E501
豬肉類/牛肉類/羊肉類/雞肉類/鴨肉類--肉類, 編F101~F501
海鮮水產類/海菜類---水產, 編G101~G201
雜貨類/乾糧類/麵食類---雜食類, 編H101~H301
這樣可以解決分類太多, 而英文碼不夠用的問題~~
4) "出貨明細表", 既然叫"明細", 就要"明明細細", 必要的資料都要掃進去,
如果因列印紙本遺失, 或大家金錢及帳上有疑慮, 都有根據核對。
5) 所有輸入都是以"品項名稱"去對應"編號"及"單價", 如果"品名"有更動, 則明細表舊有資料就對不上;
又不小心"品項"重覆了, 也有問題, 所以這部份要注意!

凡事遠處看, 就儘量幫將檔案做完整點, 往後若事業有更大規模, 再另行找專業去~~

=======================================

作者: BV7BW 時間: 2020-5-24 23:13

回復 27# 准提部林

謝謝你準大...
我先把第3版熟習1下
還有有2地方須先修改
第1客戶名稱有錯了就是說沒對準例如陳一不是A001
我有去修改客戶表好像改不過來
第2是採購表中可以修改為A001*數量
在程式中有也好像我修改不過
我再努力看看..至於項目編號可以改為這樣我再改變1下我們流程看看

我先演練幾次
再次謝謝你

作者: 准提部林 時間: 2020-5-25 17:11

回復 28# BV7BW

修改好了~~
[attach]32081[/attach]

作者: 准提部林 時間: 2020-5-26 11:51

修正"單號"以民國年判斷的錯誤:
[attach]32085[/attach]

加入"分類項目", 必須"輸入表"上方文字與"項目"表第一行完全相同才可以'
比如"餐包", 兩表必須一致!

另:帖子因有個人需求, 須予刪除, 請向此版版主或超版主請求協助~~
一般是可以用"編輯", 去刪除附件的~~

作者: BV7BW 時間: 2020-5-26 15:28

回復 30# 准提部林

謝謝準大
現在完全可運用包括增加項目及列印都是很好
我還是想在完成第1版剩下幾項這樣可作為備用
謝謝你

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)