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

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

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

¦³¨â­Ó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

¦^´_ 1# 198188
  1. Option Explicit
  2. Sub Ex()
  3.     Dim fs As String, xi As Integer
  4.     fs = "c:\user\destop\b.xlsx"
  5.     'fs = ThisWorkbook.Path & "\b.xlsx"
  6.     'Set wb = Workbooks.Open(fs)
  7.     'With Wb.Sheets("sheet1")
  8.     With Workbooks.Open("d:\book1.xls").Sheets("sheet1")
  9.       For xi = .UsedRange.Cells(.UsedRange.Count).Row To 1 Step -1
  10.              '¥Ñ¤p¨ì¤j:¤£¥² Step  ¦ý¦p­n¶¡¹j°õ¦æ¦p:  Step 5
  11.             MsgBox .UsedRange.Cells(xi, "A").Address  'AÄæ
  12.       Next
  13.     End With
  14. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE


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

TOP

¦^´_ 3# 198188
¬O¤åªk¤£¬O¤å¤Æ.
·í¤éªº¤é´Á: today()³o¬O¤u§@ªí¨ç¼Æ ,VBA ->  Date
  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\®à­±\b.xlsx"
  7.     Set Wb = Workbooks.Open(fs)
  8.     Set FRng = Wb.Sheets("sheet1").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(A).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("a.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.                 End With
  19.             End If
  20.         End If
  21.     End If
  22. End Sub
½Æ»s¥N½X
ps: ¦^ÂÐ®É «ö¤U[¥N½X] ±Nµ{¦¡½X¤º®e¶K¤W,´£¥æ,§Y¥i«K©ó¬d¬ÝªÌ,½Æ»sµ{¦¡½X.

TOP

¦^´_ 4# GBKEE


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

TOP

¦^´_ 5# 198188
°õ¦æ¤W: ¦³¿ù»~¶Ü? ¨S¦³±o¨ì§Aªº´Á±æ¶Ü?
¨S¿ù»~,¦³±o¨ì§Aªº´Á±æ,¨ºÁÙ¦³¤°»ò??

TOP

¦^´_ 6# GBKEE


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

TOP

¦^´_ 4# GBKEE

®à­±.rar (473.14 KB)

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

TOP

¦^´_ 8# 198188
½ÐÀˬd fs = "C:\Documents and Settings\USER\®à­±\b.xlsx"
USER ¬O½Ö¡A ¥¦¶È¬O´£¿ô§A¨º«üªº¬O¾Þ§@¨Ï¥ÎªÌ¡A·íµM§A´N±o´«¤W¥¿¦W¤F¡I

TOP

¥»©«³Ì«á¥Ñ 198188 ©ó 2012-12-9 09:22 ½s¿è

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

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD