Hsieh

¦^´_ 1# janejacky


    ¸Õ¸Õªþ¥ó
¥X³f³æ¾ú¥v²Î­p.rar (12.57 KB)

¤U¸ü¦¸¼Æ: 11

2011-5-9 00:03

1. Sub inputdata() 'Àx¦s¸ê®Æ
   
2. Dim Rng As Range, Ay()
   
3. With Sheet1
   
4. Set Rng = .Range("A12:A29")
   
5. If Application.CountA(Rng) > 0 Then
   
6.    For Each a In Rng.SpecialCells(xlCellTypeConstants)
   
7.       ar = Array(.[G5].Value, .[G4].Value, .[B5].Value, a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, a.Offset(, 5).Value, a.Offset(, 6).Value)
   
8.       ReDim Preserve Ay(s)
   
9.       Ay(s) = ar
   
10.       s = s + 1
    
11.    Next
    
12.    cnt = .[G32].Value
    
13.    With Sheet2
    
14.       Set a = .[A65536].End(xlUp).Offset(1)
    
15.       a.Resize(s, 8) = Application.Transpose(Application.Transpose(Ay))
    
16.       a.Offset(s - 1, 8) = cnt
    
17.    End With
    
18. End If
    
19. End With
    
20. End Sub
    
21. Function PaperNo(Rng As Range, mydate As Date, k) '¬y¤ô½s¸¹
    
22. Set d = CreateObject("Scripting.Dictionary")
    
23. mystr = Format(mydate, "yyyymmdd")
    
24. If Application.CountA(Rng) > 0 Then
    
25. For Each a In Rng.SpecialCells(xlCellTypeConstants)
    
26.     If Left(a, 8) = mystr Then d(Val(a)) = ""
    
27. Next
    
28. End If
    
29. If d.Count > 0 Then
    
30. PaperNo = IIf(k = 1, Format(Application.Max(d.keys) + 1, "00000000000"), Format(Application.Max(d.keys), "00000000000"))
    
31. Else
    
32. PaperNo = mystr & "001"
    
33. End If
    
34. End Function

½Æ»s¥N½X