- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
2#
發表於 2014-6-22 09:55
| 只看該作者
回復 1# scjiao
試試看- Option Explicit
- Sub EX()
- Dim Rng(1 To 2) As Range, E As Range
- Dim AR(), i As Integer
- With Sheets("Sheet1")
- Set Rng(1) = .Range("A6", .Range("A" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
- '讀取機總的範圍有文字的儲存閣
- End With
- ReDim AR(0 To Rng(1).Areas.Count, 1 To 4)
- AR(0, 1) = "機總"
- AR(0, 2) = "料號"
- AR(0, 3) = "期初庫存"
- AR(0, 4) = "實際進料"
- For i = 1 To Rng(1).Areas.Count
- AR(i, 1) = Rng(1).Areas(i).Cells(1)
- Set Rng(2) = Rng(1).Areas(i).Resize(, 2).Columns(2).SpecialCells(xlCellTypeConstants)
- '機總的範圍 [第2欄] 有文字的儲存閣
- For Each E In Rng(2).Areas
- If E.Range("b1") + E.Range("E5") < AR(i, 3) + AR(i, 4) Then
- '"期初庫存"+"實際進料"
- AR(i, 2) = E.Cells(1)
- AR(i, 3) = E.Range("b1") '以E.Cells(1)
- AR(i, 4) = E.Range("E5")
- ElseIf AR(i, 2) = "" Then '料號: 陣列元素=""
- AR(i, 1) = Rng(1).Areas(i).Cells(1)
- AR(i, 2) = E.Cells(1)
- AR(i, 3) = E.Range("b1")
- AR(i, 4) = E.Range("E5")
- End If
- Next
- Next
- Sheets("Sheet2").[A1].Resize(UBound(AR), UBound(AR, 2)) = AR '顯示
- End Sub
|
|
發表於 2014-6-21 23:39
| 
