各位先進你好!!小弟有一疑問想請教!!
小弟有兩個分頁要匯到同一表單上
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 |
發表於 2015-11-16 18:51
| 
