Application.EnableEvents = True ' 資料變更完畢,恢復可觸發Change程序
End If
End With
End Sub作者: 准提部林 時間: 2019-3-24 10:46
If .Address = "$A$2" Then
if not isnumeric(.value) then exit sub '不是有效數值或為錯誤值, 跳出
if .value<1 or .value>rows.count then exit sub '數值不在"列數"範圍內, 跳出
lRow = .value作者: john2006168 時間: 2019-3-24 23:12
原程式根本不可能執行~~
沒有說明需求流程, 先做個樣:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xF As Range, Cr
With Target
If .Address <> "$A$2" Then Exit Sub
If .Value = "" Then Exit Sub
Set xF = [Data!H:H].Find(.Value, Lookat:=xlWhole)
If xF Is Nothing Then Exit Sub
Application.EnableEvents = False
Cr = Array(2, 9, 8, 6, 7, 1, 16, 17, 18, 19, 20)
For j = 1 To 11
Cells(5, j + 1) = Sheets("Data").Cells(xF.Row, Cr(j - 1)).Value
Next j
Application.EnableEvents = True
End With
End Sub作者: john2006168 時間: 2019-3-25 16:51
不清不楚, 矇著寫:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xF As Range, Cr, DNo1&, dNo2&, xE As Range
With Target
If .Address <> "$A$2" Then Exit Sub
If .Value = "" Then Exit Sub
Set xF = [F:F].Find(.Value, Lookat:=xlWhole, SearchDirection:=xlPrevious) '取得最後一筆 shipment ref
If Not xF Is Nothing Then DNo1 = Val(xF(1, -3)) + 1 '得最後一筆 shipment ref 的 batch no +1
Set xF = [Data!H:H].Find(.Value, Lookat:=xlWhole)
If xF Is Nothing Then Exit Sub
Application.EnableEvents = False
Cr = Array(9, 2, 9, 8, 6, 7, 1, 16, 17, 18, 19, 20, 21)
Set xE = Cells(Rows.Count, 3).End(xlUp)(2)
If xE.Row < 5 Then Set xE = [C5]
For j = 1 To 13
xE(1, j) = Sheets("Data").Cells(xF.Row, Cr(j - 1)).Value
Next j
If Not IsDate(xE(1, 2)) Then Exit Sub
dNo2 = Format(xE(1, 2), "yymm") * 1000 + 1
xE(1, 0) = Application.Max(DNo1, dNo2)
