198188

¦^´_ 4# GBKEE


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

GBKEE

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

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

GBKEE

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