求助~排程填入日期 - Excel程式區

[發問] 求助~排程填入日期

1^# 跳轉到 »

發表於 2013-1-11 14:14 | 顯示全部帖子

本帖最後由 stillfish00 於 2013-1-11 14:15 編輯

回復 6# adam2010
試試看

Sub Test()
Dim rng As Range
Dim s1 As Long, s2 As Long
Dim cindex As Long
With Sheets(2)
Set rng = .Range(.[A1], .[A1].End(xlToRight).End(xlDown).Offset(, -1)) '[出貨日]資料範圍
End With
For Each c In Sheets(1).[E2:E15] '[交期]資料填入範圍
If c.Offset(, -4).Value <> c.Offset(-1, -4).Value Then
cindex = 1
s1 = 0 '累積至前一批數量
s2 = 0 '累積出貨需求數量
Else
s1 = s1 + c.Offset(-1, -1).Value
End If
Do While s2 <= s1 And cindex < rng.Columns.Count
cindex = cindex + 1
s2 = s2 + Application.VLookup(c.Offset(, -4).Value, rng, cindex, False)
Loop
If s2 <= s1 Then
c.Value = "NA"
Else
c.Value = rng.Cells(1, cindex).Value
End If
Next
Set rng=Nothing
End Sub

複製代碼

2^#

發表於 2013-1-12 17:36 | 顯示全部帖子

回復 8# adam2010

Sub Test()
Dim rng As Range
Dim s1 As Long, s2 As Long
Dim cindex As Long
With Sheets("出貨日")
Set rng = .Range(.[A1], .[A1].End(xlToRight).End(xlDown).Offset(, -1)) '[出貨日]資料範圍
End With
For Each c In Sheets("交期").Range("E2:E" & Sheets("交期").[A1].End(xlDown).Row) '[交期]資料填入範圍
If c.Offset(, -4).Value <> c.Offset(-1, -4).Value Then
cindex = 1
s1 = 0 '累積至前一批數量
s2 = 0 '累積出貨需求數量
Else
s1 = s1 + c.Offset(-1, -1).Value
End If
Do While s2 <= s1
cindex = cindex + 1
If Application.IsError(Application.VLookup(c.Offset(, -4).Value, rng, cindex, False)) Then
Exit Do
Else
s2 = s2 + Application.VLookup(c.Offset(, -4).Value, rng, cindex, False)
End If
Loop
If s2 <= s1 Then
c.Value = "NA"
Else
c.Value = rng.Cells(1, cindex).Value
End If
Next
Set rng = Nothing
End Sub

複製代碼

靜思自在 : 慈悲沒有敵人，智慧不起煩惱。