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

c_c_lai

¦^´_ 10# 198188
°²³] K Äæ­ì¥»¤§¤é´Á¬°  12/8¡A ¬°°t¦X´ú¸Õ±N¥¦­Ì§¡§ï¦¨ 12/9 ¤µ¤é¡C
Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious)
«h FRng ·j´Mµ²ªG¬O«ü¦V FRng.Address : "$K$28" ¸Ó±ø¥ó§k¦X¤§³Ì«á¤@¶µ¡A
¦Ó¸Ó¦C H Äæ FRng.Offset(, -3).Value : 0 (¸ÓÄæ«YªÅ¥ÕÄæ)¡A
µ²½×¬O If FRng.Offset(, -3).Value >= 0.95 Then ¤£¦¨¥ß¡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¿ù?

c_c_lai

¦^´_ 13# 198188

¤U¸ü (47.1 KB)

2012-12-9 20:11

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

c_c_lai

¦^´_ 16# 198188
§A¥i¥H°Ñ¦Ò kai6929 ¥ý¶i´£¨Ñªº¡G
¾P°âºÞ²z§¹¦¨ª©
#19 ªþ¥ó¤º¦³Ãöªº AdvancedFilter  ªº¹LÂo±ø¥óªº³B²z¡C
¤§«á¦A¨Ó«äºû Outstanding Payments ±µ¤U¨Óªº°Ê§@¡C

stillfish00

¦^´_ 16# 198188
¹ï¨ç¼Æ(Find)¥Îªk¤£²M·¡®É , ¥i¥H¦Û¤v¥ý¬d¸ßExcel»¡©ú ,
³o¼Ë¥i¥H§ó¤F¸Ñ¥¦ªº¦U°Ñ¼Æ¥Î³~ , ¸Ì­±¤]¦³¨Ò¤l¥i°Ñ¦Ò

Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious)
³o¦æ¥u¬O ¦bKÄæ§ä¨ì³Ì«á¤@µ§²Å¦XDateªºÀx¦s®æ ,
¦³§ä¨ì -> FRng³]¬°¸ÓÀx¦s®æ
¨S§ä¨ì -> FRng = nothing
´N¥u§ä¤@¦¸¦Ó¤w

§A­n¦b¨C­Ó²Å¦XDateªºÀx¦s®æ³£¥t¥~§PÂ_±ø¥ó
¥i¥H°Ñ¦Ò Excel»¡©úªº½d¨Ò¥h§ï¼g
½d¨Ò :
        ³o­Ó½d¨Ò·|¦b²Ä¤@±i¤u§@ªí¤Wªº A1:A500 ½d³ò¤º´M§ä­È¬° 2 ªº©Ò¦³Àx¦s®æ¡A¨Ã±N³o¨ÇÀx¦s®æªº­ÈÅÜ§ó¬° 5¡C

1. With Worksheets(1).Range("a1:a500")
   
2.     Set c = .Find(2, lookin:=xlValues)
   
3.     If Not c Is Nothing Then
   
4.         firstAddress = c.Address
   
5.         Do
   
6.             c.Value = 5
   
7.             Set c = .FindNext(c)
   
8.         Loop While Not c Is Nothing And c.Address <> firstAddress
   
9.     End If
   
10. End With

½Æ»s¥N½X

198188

¦^´_ 18# stillfish00

1. Option Explicit
   
2. 
   
3. Sub ex()
   
4.    
   
5.     Dim FRng As Range, Wb As Workbook
   
6.     Dim FirstAddress As String
   
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.         
    
19.         If FRng.Offset(, -3).Value >= 0.95 And FRng.Offset(, -3).Value >= 0.95 Then '°²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95
    
20. 
    
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 Rng 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
    
43.    

½Æ»s¥N½X

¦ý¬O§Ú²{¦b³s²Ä¤@­Ó³£¨S¦³¤ÏÀ³¡A¬O¤£¬Oµ{¦¡¦³¿ù¡H
§Ú§â¥¦ªº¤é´Á¼g¤W·í¤Ñ¡AµM«á¦Ê¥÷¤ñ¶ñ¤W100% ¡A·Ó±`²zÀ³¸Ó209514 ·|¥X²{¦boutstanding payments sheetªºA21Àx¦s®æ¤W¡A¦ý¬O¨S¦³¡A¬O¤£¬Oµ{¦¡¦³¿ù¡H
209514        Argentia        MEIKE         7,936.50                         100.00%                        12/11        Lily Chan

stillfish00

¦^´_ 19# 198188
1. §Aªºcode¬O©ñ¦b­þ­ÓÀÉ®×?
2. ²Ä22¦æ:  Set Rng = Workbooks("outstanding payments").Sheets("outstanding payments").Range("a:a").Find(FRng.Offset(, -9), lookat:=xlWhole, SearchDirection:=xlPrevious)
     §ïoutstanding payments.xlsm
3.  ²Ä26¦æ:  With Workbooks.Open("outstanding payments.xlsm").Sheets("outstanding payments")
     §ï¥Î§¹¾ã¸ô®|¬Ý¬Ý
4. µ{¦¡¤£¦p¹w´Á , ¥i¥H¾Ç²ß¤U¤¤Â_ÂI©Î³v¦æ¶] , ·f°t°Ï°ìÅܼƵøµ¡&§Y®É¹Bºâµøµ¡ , debug·|§ó¦³®Ä²v