½ÐÀ°¦£Â½Ä¶¤@¤Uµ{¦¡

198188

¦³¨â­Óexcel¡A¤À§O¬Oa.xlsx ©Mb.xlsx
§Ú·Q¦ba.xlsx¨Çvba, Ū¨úb.xlsxªº¸ê®Æ¡C
½Ð°Ý§ÚÀ³¸Ó¦p¦ó¼g
for i = b.xlsxªº³Ì«á¤@µ§ to 1
¦]¬°§Ú·Q¥Îb.xlsx³Ì«á¤@Äæ¶}©l©¹¤WÀˬda.xlsx
¦ý¤£ª¾¹D«ç¼Ë¼g¤~¥i¥Hª¾¹Db.xlsxªº³Ì«á¤@µ§

Sub ex()
Dim FRng As Range
Dim A As Range, Rng As Range
  fs = "c:\user\destop\b.xlsx"
'fs = ThisWorkbook.Path & "\b.xlsx"
set wb = Workbooks.Open(fs)
     Set FRng = wb.Sheets("sheet1").Range("k:k").Find(today(), lookat:=xlWhole, SearchDirection:=xlPrevious) ¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C
     If Not FRng Is Nothing Then °²¦p§ä¨ì
        If FRng.Offset(, -3).Value >= 0.95 Then °²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
set rng = workbooks(a).range("a:a").find(Frng.offset(,-9), lookat:=xlWhole, SearchDirection:=xlPrevious)     ¦ba.xlsx ªºAÄæ´M§äb.xlsx ³o¦CªºBÄ檺­È¬O§_¦s¦b
If FRng Is Nothing Then  °²¦p§ä¤£¨ì
                ¦ba.xlsx ªºAÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºBÄ檺­È¡A¤Î   ¦ba.xlsx ªºFÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºKÄ檺­È

end sub

198188

¦^´_ 2# GBKEE

¤U¸ü (157.47 KB)
a.xlsm

2012-12-8 11:58

¤U¸ü (134.93 KB)
b.xlsx

2012-12-8 11:58

Sub ex()
Dim FRng As Range
Dim A As Range, Rng As Range
Dim fs As String, xi As Integer
   fs = "C:\Documents and Settings\USER\®à­±\b.xlsx"
  set wb = Workbooks.Open(fs)
     Set FRng = wb.Sheets("sheet1").Range("k:k").Find(today(), lookat:=xlWhole, SearchDirection:=xlPrevious) ¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C
        If Not FRng Is Nothing Then °²¦p§ä¨ì
        If FRng.Offset(, -3).Value >= 0.95 Then °²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
  set rng = workbooks(a).range("a:a").find(Frng.offset(,-9), lookat:=xlWhole, SearchDirection:=xlPrevious)     ¦ba.xlsx ªºAÄæ´M§äb.xlsx ³o¦CªºBÄ檺­È¬O§_¦s¦b
         If FRng Is Nothing Then  °²¦p§ä¤£¨ì
          With Workbooks.Open("a.xlsm").Sheets("outstanding payments")    ¦ba.xlsx ªºAÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºBÄ檺­È¡A¤Î   ¦ba.xlsx ªºFÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºKÄ檺­È
           xi = .UsedRange.Cells(.UsedRange.Count).Row
             .UsedRange.Cells(xi, "A") = FRng.offset(, -9).value
           .UsedRange.Cells(xi, "F") = FRng.value
         End With
         End If
     End If
End If
End Sub

½Ð°Ýµ{¦¡¤å¤Æ¦³°ÝÃD¶Ü¡H

198188

¦^´_ 4# GBKEE


    ½Ð°Ý°£¤FTodayªº»yªk¦³°ÝÃD¥~,ÁÙ¦³¨S¦³¨ä¥L¦a¤è¦³»yªk°ÝÃD?

198188

¦^´_ 6# GBKEE


    ÁÙ¨S¸Õ!¦]¬°§Ú¦b¥~­±¥Î¤â¾÷¤WºôµLªk´ú¸Õ!ÁöµM©ú¥Õ·§©À¦ý¦]¬°¦Û¤v»yªk¯à¤O®z,Ãø¥Hªí¹F,©Ò¥H¥ý°Ý²M·¡,¦^¥h¸Õ¥i¥H»´ÃP¨Ç

198188

¦^´_ 4# GBKEE

®à­±.rar (473.14 KB)

¤U¸ü¦¸¼Æ: 7

2012-12-8 22:49

¥u¬O¥´¶}¤F¥t¥~¤@­ÓExcel¡A¨ä¥L§¹¥þ¨S¦³¤ÏÀ³¡C   
¬O¤£¬O¦³¨º¨Ç¦a¤è¥X¿ù¤F¡H

198188

¦^´_ 9# c_c_lai

C:\Documents and Settings\USER\®à­±
    §Ú¬O¥Îuserªº¦WºÙ¨Ó©R¦W³o¥x¹q¸£¡A©Ò¥H³o¥y¨S¿ù¡C
§Ú­n¶}ªº¥t¤@­Óexcel ¡]b.xlsx¡^¬O¥´¶}¤F¡A¦ý¬O°õ¦æ¨S¦³¤°»ò°µ¥X¨Ó

198188

¦^´_ 9# c_c_lai

¤U¸ü (139.91 KB)
payment report 2012

2012-12-9 09:41

¤U¸ü (135.82 KB)
outstanding payment

2012-12-9 09:41

1. Option Explicit
   
2. 
   
3. Sub ex()
   
4. 
   
5.     Dim FRng As Range, Wb As Workbook
   
6. 
   
7.     Dim A As Range, Rng As Range
   
8. 
   
9.     Dim fs As String, xi As Integer
   
10.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"
    
11. 
    
12.     Set Wb = Workbooks.Open(fs)
    
13. 
    
14.     Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) '¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C
    
15. 
    
16.     If Not FRng Is Nothing Then                 '°²¦p§ä¨ì
    
17. 
    
18.         If FRng.Offset(, -3).Value >= 0.95 Then '°²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
    
19. 
    
20.             '*** Workbooks(A)-> A³oÅܼƦr¦ê¨S¦³¨£¨ì  ****
    
21. 
    
22.             Set Rng = Workbooks("outstanding payments").Sheets("outstanding payments").Range("a:a").Find(FRng.Offset(, -9), lookat:=xlWhole, SearchDirection:=xlPrevious)   ' ¦ba.xlsx ªºAÄæ´M§äb.xlsx ³o¦CªºBÄ檺­È¬O§_¦s¦b
    
23. 
    
24.             If FRng Is Nothing Then             '°²¦p§ä¤£¨ì
    
25. 
    
26.                 With Workbooks.Open("outstanding payments.xlsm").Sheets("outstanding payments")  '   ¦ba.xlsx ªºAÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºBÄ檺­È¡A¤Î   ¦ba.xlsx ªºFÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºKÄ檺­È
    
27. 
    
28.                     xi = .UsedRange.Cells(.UsedRange.Count).Row
    
29. 
    
30.                     .UsedRange.Cells(xi, "A") = FRng.Offset(, -9).Value
    
31. 
    
32.                     .UsedRange.Cells(xi, "F") = FRng.Value
    
33.                   
    
34.                 End With
    
35. 
    
36.             End If
    
37. 
    
38.         End If
    
39. 
    
40.     End If
    
41. Wb.Close 0
    
42. End Sub

½Æ»s¥N½X

®ÄªG¬O·Q·ípayment report 2012 ¤ºKÄæ¬O¤µ¤Ñªº¤é´Á¡A¦ÓHÄ檺%¹F¨ì95%©Î¥H¤W¡A¥[¤WBÄ檺 SO#¦boutstanding payments ªí¤º¨S¦³ªº¸Ü¡A´N§âpayment report 2012 ¤ºHÄ檺%¹F¨ì95%©Î¥H¤W¡A¤ÎBÄ檺 SO#¦boutstanding payments ªí¤º¨S¦³ªºSO¡­ ©ñ¦boutstanding payments ªí¤ºªºAÄ檺³Ì«á¤@¦C¤W ¤ÎFÄ檺¤é´Ácopy¹L¥h¬ÛÀ³¦ì¸m¡C
¦p¹Ï¤¤outstanding payment ¶À¦âªº³¡¤À¡C

198188

¦^´_ 12# c_c_lai


    ¦³ªÅ¦C´NµLªk¹B¦æ?¦ý¬O§Ú¤§«e¤]¥Î³o¥y´M§äBÄæ¦p¦³¦ÓHÄæ¦Ê¥÷¤ñ¹F¤E¤Q¤­©Î¥H¤W´N±NKÄæ¤é´Á¶Ç¦^!¦P¼Ë¦³ªÅ¦C¦ý¹B§@´NµL°ÝÃD,¬O¦ó³B¥X¿ù?

198188

¦^´_ 14# c_c_lai


    §Ú¸Õ¹L±NH28§ï¦¨20% ©ÎªÌ100%¡A¦ý¬O¤´µM¨S¦³¤ÏÀ³¡C
¥t¥~Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) ¬O¤£¬O¤£°±¦a¦V¤W§ä¡H¨º»òK28¤@¦C¹F¤£¨ì­n¨D¡A¤£¬O·|¦A¦V¤W´M§ä¶Ü¡H

198188

¦^´_ 4# GBKEE

1. ption Explicit
   
2. 
   
3. Sub ex()
   
4. 
   
5.     Dim FRng As Range, Wb As Workbook
   
6. 
   
7.     Dim A As Range, Rng As Range
   
8. 
   
9.     Dim fs As String, xi As Integer
   
10.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"
    
11. 
    
12.     Set Wb = Workbooks.Open(fs)
    
13. 
    
14.     Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) '¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C
    
15. 
    
16.     If Not FRng Is Nothing Then                 '°²¦p§ä¨ì
    
17. 
    
18.         If FRng.Offset(, -3).Value >= 0.95 [color=Red]And FRng.Offset(, -3).Value <> " "[/color] Then  '°²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
    
19. 
    
20.             Set Rng = Workbooks("outstanding payments").Sheets("outstanding payments").Range("a:a").Find(FRng.Offset(, -9), lookat:=xlWhole, SearchDirection:=xlPrevious)   ' ¦ba.xlsx ªºAÄæ´M§äb.xlsx ³o¦CªºBÄ檺­È¬O§_¦s¦b
    
21. 
    
22.             [color=Red]If Rng Is Nothing Then             [/color]'°²¦p§ä¤£¨ì
    
23. 
    
24.                 [color=Red]With Workbooks("outstanding payments").[/color]Sheets("outstanding payments")  '   ¦ba.xlsx ªºAÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºBÄ檺­È¡A¤Î   ¦ba.xlsx ªºFÄæ³Ì«á¤@¦C¥[¤Wb.xlsx ³o¦CªºKÄ檺­È
    
25. 
    
26.                     xi = .UsedRange.Cells(.UsedRange.Count).Row
    
27. 
    
28.                     .UsedRange.Cells(xi, "A") = FRng.Offset(, -9).Value
    
29. 
    
30.                     .UsedRange.Cells(xi, "F") = FRng.Value
    
31.                   
    
32.                 End With
    
33. 
    
34.             End If
    
35.            
    
36.         End If
    
37. 
    
38.     End If
    
39.    Wb.Close 0
    
40. End Sub

½Æ»s¥N½X

¤§«eIf Rng Is Nothing Then   ¼g¿ù¦¨ If FRng Is Nothing Then ¡A¦ý¬O§ï§¹¤F¤]¨S¦³¤ÏÀ³¡A¥u¬O¥´¶}¥t¤@­Óexcel«á´N¨S¦³¤°»ò¤ÏÀ³¡A¬O­þ¸Ì¥X²{°ÝÃD¤F¡A½Ð«ü©w°g¬z¡C ¤£ºÞ³Ì«á¤@¦C¬O§_HÄæ¬O§_ªÅ®æ©ÎªÌ¦Ê¥÷¤§´X³£¨S¦³¤ÏÀ³¡A¥t¥~ Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) ¬O§_·|¤£°±¦a©¹¤W´M§ä,ª½¨ì²Ä¤@¦C¬°¤î¡H