ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

¦^´_ 9# c_c_lai

payment report 2012

payment report 2012.JPG
2012-12-9 09:41

outstanding payment

Outstanding Payments.JPG
2012-12-9 09:41
  1. Option Explicit

  2. Sub ex()

  3.     Dim FRng As Range, Wb As Workbook

  4.     Dim A As Range, Rng As Range

  5.     Dim fs As String, xi As Integer
  6.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"

  7.     Set Wb = Workbooks.Open(fs)

  8.     Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) '¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C

  9.     If Not FRng Is Nothing Then                 '°²¦p§ä¨ì

  10.         If FRng.Offset(, -3).Value >= 0.95 Then '°²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95

  11.             '*** Workbooks(A)-> A³oÅܼƦr¦ê¨S¦³¨£¨ì  ****

  12.             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

  13.             If FRng Is Nothing Then             '°²¦p§ä¤£¨ì

  14.                 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Ä檺­È

  15.                     xi = .UsedRange.Cells(.UsedRange.Count).Row

  16.                     .UsedRange.Cells(xi, "A") = FRng.Offset(, -9).Value

  17.                     .UsedRange.Cells(xi, "F") = FRng.Value
  18.                   
  19.                 End With

  20.             End If

  21.         End If

  22.     End If
  23. Wb.Close 0
  24. 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

TOP

¦^´_ 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

TOP

¦^´_ 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¿ù?

TOP

¦^´_ 13# 198188
2.png
2012-12-9 20:11

TOP

¦^´_ 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

TOP

¦^´_ 4# GBKEE
  1. ption Explicit

  2. Sub ex()

  3.     Dim FRng As Range, Wb As Workbook

  4.     Dim A As Range, Rng As Range

  5.     Dim fs As String, xi As Integer
  6.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"

  7.     Set Wb = Workbooks.Open(fs)

  8.     Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) '¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C

  9.     If Not FRng Is Nothing Then                 '°²¦p§ä¨ì

  10.         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

  11.             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

  12.             [color=Red]If Rng Is Nothing Then             [/color]'°²¦p§ä¤£¨ì

  13.                 [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Ä檺­È

  14.                     xi = .UsedRange.Cells(.UsedRange.Count).Row

  15.                     .UsedRange.Cells(xi, "A") = FRng.Offset(, -9).Value

  16.                     .UsedRange.Cells(xi, "F") = FRng.Value
  17.                   
  18.                 End With

  19.             End If
  20.            
  21.         End If

  22.     End If
  23.    Wb.Close 0
  24. 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

TOP

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

TOP

¦^´_ 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

TOP

¦^´_ 18# stillfish00
  1. Option Explicit

  2. Sub ex()
  3.    
  4.     Dim FRng As Range, Wb As Workbook
  5.     Dim FirstAddress As String
  6.     Dim A As Range, Rng As Range

  7.     Dim fs As String, xi As Integer
  8.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"

  9.     Set Wb = Workbooks.Open(fs)

  10.     Set FRng = Wb.Sheets("New form of payment report").Range("k:k").Find(Date, lookat:=xlWhole, SearchDirection:=xlPrevious) '¦bb.xlsx ªºKÄæ´M§äµ¥·í¤éªº¤é´Áªº¤@¦C

  11.     If Not FRng Is Nothing Then '°²¦p§ä¨ì

  12.         
  13.         If FRng.Offset(, -3).Value >= 0.95 And FRng.Offset(, -3).Value >= 0.95 Then '°²¦pb.xlsx ³o¦CªºHÄ檺­È¤j¹L©Îµ¥©ó0.95

  14.            
  15.             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

  16.             If Rng Is Nothing Then             '°²¦p§ä¤£¨ì

  17.                 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Ä檺­È

  18.                     xi = .UsedRange.Cells(.UsedRange.Count).Row

  19.                     .UsedRange.Cells(xi, "A") = FRng.Offset(, -9).Value

  20.                     .UsedRange.Cells(xi, "F") = FRng.Value
  21.                   
  22.                 End With

  23.             End If

  24.         End If
  25.      
  26.     End If
  27. Wb.Close 0
  28. End Sub
  29.    
½Æ»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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2012-12-12 00:57 ½s¿è

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¹ï¤÷¥À­nª¾®¦¡A·P®¦¡B³ø®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD