|
Sub cp()
Dim p, f, ID, wb, rng
Application.ScreenUpdating = False
p = ThisWorkbook.Path & "\"
f = "Test-FQC.xls"
ID = [G1]
Call CloseWorkbook(f)
Set wb = Workbooks.Open(p & f)
Set rng = Range("a:a").Find(ID)
If rng Is Nothing Then
Call OneRow(wb)
Else
MsgBox "Lot ID Already exist.", , ID
End If
wb.Close True
End Sub
Sub OneRow(wb)
Dim A
Dim B(1 To 1, 1 To 58) '58 = 1 + 1 + 1 + 5 * 11
Dim i, j, k
ThisWorkbook.Activate
A = Range("a1").CurrentRegion
B(1, 1) = [G1]
B(1, 2) = [C1]
B(1, 3) = [E1]
k = 3
For i = 3 To UBound(A)
For j = 1 To 5
k = k + 1
B(1, k) = IIf(j > A(i, 11), "", A(i, j + 5))
Next j
Next i
wb.Activate
i = Cells(Rows.Count, 2).End(xlUp).Row + 1
Cells(i, 1).Resize(1, UBound(B, 2)) = B
End Sub
Sub CloseWorkbook(f)
On Error Resume Next
Workbooks(f).Close 0
On Error GoTo 0
End Sub
f.rar
(33.42 KB, 下载次数: 10)
|
|