- 帖子
- 253
- 主題
- 5
- 精華
- 0
- 積分
- 308
- 點名
- 0
- 作業系統
- W10
- 軟體版本
- Excel 2016
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2019-6-16
- 最後登錄
- 2024-3-20
|
本帖最後由 軒云熊 於 2021-1-24 18:56 編輯
回復 1# stephenlee
參考了 準大的寫法 順便練習 有空的話也順便幫我看看 是否正常 感謝- Public Sub 資料分拆練習()
- Application.ScreenUpdating = False
- Arr = [A1].CurrentRegion
- Ar = [{900, "1,2", "3,4", "5"; 900, "1,2", "3,4","-"; 1800, "Support","-","-"}]
- Sheets.Add(After:=Sheets(1)).Name = "結果" & Format(Now, "-YYYY-MM-DD")
- For Y = 1 To UBound(Arr, 2): Cells(1, Y) = Arr(1, Y): Next Y
- For X = 1 To UBound(Ar)
- A = Int(Arr(2 + k, 4) / Ar(X, 1)) + 1
- C = Arr(2 + k, 4) - (A - 1) * Ar(X, 1)
- For i = 2 To UBound(Ar, 2)
- If Ar(X, i) <> "-" Then
- For Y = 1 To A
- If u < A Then u = u + 1
- E = IIf(u = A, C, Ar(X, 1))
- Cells(2 + G, 1) = Ar(X, i)
- Cells(2 + G, 2) = Arr(2 + k, 2)
- Cells(2 + G, 3) = Arr(2 + k, 3)
- Cells(2 + G, 4) = E
- Cells(2 + G, 5) = Arr(2 + k, 5)
- Cells(2 + G, 6) = Arr(2 + k, 6)
- G = G + 1
- Next Y
- End If
- u = 0
- Next i
- k = k + 1
- Next X
- Application.ScreenUpdating = True
- End Sub
|
|
發表於 2021-1-24 18:54
| 