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

標題: [發問] VBA 選擇先後順序的語法 [打印本頁]

作者: fantersy 時間: 2015-11-16 18:51 標題: VBA 選擇先後順序的語法

各位先進你好!!小弟有一疑問想請教!!
小弟有兩個分頁要匯到同一表單上
A分頁10個項目
B分頁有5個項目
匯到分頁時要能先從A分頁開始匯入後面再貼B分頁
當B分頁項目大於A分頁項目一樣先貼A再貼B分頁
但小弟寫不出語法!!麻煩請指教
!!重點在Select Case Value 以下的語法!!
拜託了...
Sub D03匯入個人管制表()
Dim A(1000), B(1000), C(1000), D(1000), E(1000), A1(1000), B1(1000), C1(1000), D1(1000), E1(1000)

Sheets("D03生產計劃表").Select
X = 5
Do While Cells(X, 1) <> ""
A(X) = Cells(X, 12)
B(X) = Cells(X, 13)
C(X) = Cells(X, 1)
D(X) = Cells(X, 2)
E(X) = Cells(X, 14)
X = X + 1
Loop

Sheets("陶瓷電木生產計劃表").Select
X1 = 5
Do While Cells(X1, 1) <> ""
A1(X1) = Cells(X1, 12)
B1(X1) = Cells(X1, 13)
C1(X1) = Cells(X1, 1)
D1(X1) = Cells(X1, 2)
E1(X1) = Cells(X1, 14)
X1 = X1 + 1
Loop

Sheets("繞線-個人管製表改").Select

If X > X1 Then
Y = X * 6
Else
Y = X1 * 6
End If
X3 = 5
X4 = 5

For Y1 = 1 To Y
Cells(Y1, 1) = ""
Cells(Y1 + 1, 1) = "繞線個人分配管制表"
Cells(Y1 + 2, 1) = "作業者"
Cells(Y1 + 2, 2) = "分配碼"
Cells(Y1 + 2, 3) = "製令單號"
Cells(Y1 + 2, 4) = "產品產號"
Cells(Y1 + 2, 5) = "分配量"
Cells(Y1 + 2, 6) = "生產日"
Cells(Y1 + 2, 7) = "生產量"
Cells(Y1 + 2, 8) = "假日"
Cells(Y1 + 2, 9) = "不良1"
Cells(Y1 + 2, 10) = "不良2"
Cells(Y1 + 2, 11) = "不良3"
Cells(Y1 + 2, 12) = "不良4"
Cells(Y1 + 2, 13) = "不良5"
Cells(Y1 + 2, 14) = "不良6"
Cells(Y1 + 2, 15) = "目檢員"
Cells(Y1 + 2, 16) = "登錄"

Select Case Value
      Case X <= X1

         If Y1 <= (X + X1) Then
            Cells(Y1 + 3, 1) = A(X3)
            Cells(Y1 + 3, 2) = B(X3)
            Cells(Y1 + 3, 3) = C(X3)

            Cells(Y1 + 3, 4) = D(X3)
            Cells(Y1 + 3, 5) = E(X3)
            X3 = X3 + 1
         Else
            Cells(Y1 + 3, 1) = A1(X4)
            Cells(Y1 + 3, 2) = B1(X4)
            Cells(Y1 + 3, 3) = C1(X4)
            Cells(Y1 + 3, 4) = D1(X4)
            Cells(Y1 + 3, 5) = E1(X4)
            X4 = X4 + 1
         End If

      Case X < X1

         If Y1 <= (X1 + X) - 6 Then
            Cells(Y1 + 3, 1) = A(X3)
            Cells(Y1 + 3, 2) = B(X3)
            Cells(Y1 + 3, 3) = C(X3)
            Cells(Y1 + 3, 4) = D(X3)
            Cells(Y1 + 3, 5) = E(X3)
            X3 = X3 + 1
         Else
            Cells(Y1 + 3, 1) = A1(X4)
            Cells(Y1 + 3, 2) = B1(X4)
            Cells(Y1 + 3, 3) = C1(X4)
            Cells(Y1 + 3, 4) = D1(X4)
            Cells(Y1 + 3, 5) = E1(X4)
            X4 = X4 + 1
         End If

End Select

Y1 = Y1 + 5
Next

Y = 2
Do While Cells(Y, 1) <> ""

Y = Y + 1

Loop

End Sub

作者: fantersy 時間: 2015-11-17 09:16

抱歉!!補上檔案!
拜託各位先進囉!!
[attach]22462[/attach]

作者: GBKEE 時間: 2015-11-17 10:11

回復 2# fantersy
A分頁10個項目
B分頁有5個項目
A,B 的項目不同嗎? (各是哪些項)

匯到分頁時要能先從A分頁開始匯入後面再貼B分頁
當B分頁項目大於A分頁項目一樣先貼A再貼B分頁可解釋嗎

附檔最好有你期望的範例

作者: fantersy 時間: 2015-11-17 10:31

回復 3# GBKEE

大大你好
A分頁與B分頁的項目數量的確不同
巨集內容小弟已將要讀取的項目都已寫好
如先前所提到...若A分頁項目大於B分頁或等於小於
都要能依序從A分頁的資料開始貼...之後在從B分頁開始貼
匯到繞線-個人管製表改分頁後可以看到分配碼(流水碼)
從1開始貼
語法從
Sheets("繞線-個人管製表改").Select......
開始就是貼的動作
小弟這樣說明不知是否清楚??
另外!!大大提的附件指的是??

作者: fantersy 時間: 2015-11-17 12:27

[attach]22466[/attach]回復 4# fantersy

已上傳
想要的結果在想要的結果分頁中
有標示黃色區塊
區塊中可以看到分配碼

原本匯入的分頁分配碼會被蓋掉

不知這樣解釋是否清楚

作者: GBKEE 時間: 2015-11-17 13:38

回復 5# fantersy
試試看

Option Explicit
Sub Ex()
Dim Ac(), xAr(), Ar(), Rng As Range, i As Integer, ii As Integer, e As Variant
Ac = Array(12, 13, 1, 2, 14) '指定欄位順序
i = 0
For Each e In Array("D03生產計劃表", "陶瓷電木生產計劃表")
Set Rng = Sheets(e).Range("A5") '起始列
Do While Rng <> ""
ReDim Ar(UBound(Ac)) '重置陣列:元素數=Ac
ReDim Preserve xAr(0 To i) 'Preserve 選擇性引數。當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字。
For ii = 0 To UBound(Ac) '
Ar(ii) = Rng(1, Ac(ii)) '=> 'Ar(ii) = Rng.Cells(1, Ac(ii))
Next
xAr(i) = Ar '資料存入
Set Rng = Rng.Offset(1)
i = i + 1
Loop
Next
Ar = Array("作業者", "分配碼", "製令單號", "產品產號", "分配量", "生產日", "生產量", "假日", "不良1", "不良2", "不良3", "不良4", "不良5", "不良6", "目檢員", "登錄")
With Sheets("繞線-個人管製表改")
.UsedRange.Clear
Set Rng = .Range("A2")
For Each e In xAr '讀取資料
'=> For e = 0 To UBound(xAr)
With Rng
.Range("a1") = "繞線個人分配管制表"
.Range("a2").Resize(, UBound(Ar) + 1) = Ar
.Range("a3").Resize(, UBound(e) + 1) = e
'=>.Range("a3").Resize(, UBound(xAr(e)) + 1) = xAr(e)
End With
Set Rng = Rng.Offset(7) '下移7列
Next
End With
End Sub

複製代碼

作者: fantersy 時間: 2015-11-17 14:16

回復 6# GBKEE
感謝版大的回覆
這好像是陣列的寫法是嗎??

作者: 准提部林 時間: 2015-11-17 21:10

多個參考，與超板語法大致類似：
Sub TEST()
Dim xS As Worksheet, Clmn, Arr, Brr, j&, k%, xArea As Range
With Sheets("繞線-個人管製表改")
　　.UsedRange.Clear
　　.[A2] = "繞線個人分配管制表"
　　.[A3:P3] = Split("作業者,分配碼,製令單號,產品產號,分配量,生產日,生產量,假日,不良1,不良2,不良3,不良4,不良5,不良6,目檢員,登錄", ",")
　　Set xArea = .[A2:P4]: Brr = xArea
End With
　
Clmn = Array(12, 13, 1, 2, 14)
For Each xS In Sheets(Array("D03生產計劃表", "陶瓷電木生產計劃表"))
　　Arr = Range(xS.[A1], xS.UsedRange).Offset(4, 0)
　　For j = 1 To UBound(Arr)
　　　　If Arr(j, 1) = "" Then Exit For
　　　　For k = 0 To UBound(Clmn): Brr(3, k + 1) = Arr(j, Clmn(k)): Next
　　　　xArea = Brr: Set xArea = xArea.Offset(6, 0)
　　Next j
Next
End Sub

作者: fantersy 時間: 2015-11-18 16:26

回復 8# 准提部林

感謝大大的分享!!

作者: fantersy 時間: 2015-11-18 16:28

回復 6# GBKEE

版大請問一下!!若我想要a1:p5加框線
語法
            .Range("a1", "p5").Select
            .Borders(xlEdgeLeft).Weight = xlMedium
            .Borders(xlEdgeTop).Weight = xlMedium
            .Borders(xlEdgeBottom).Weight = xlMedium
            .Borders(xlEdgeRight).Weight = xlMedium
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideHorizontal).Weight = xlThin
但我跑不出來!!請問是否有錯??
小弟有看到你用跳7格的做法
一樣每一次都要劃框線的話該如何撰寫??

作者: fantersy 時間: 2015-11-18 17:39

改好了!!
感謝
            With .Range("a1", "p5").Borders
            .Weight = 2
            End With

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