198188

¦^´_ 20# stillfish00


1. §Aªºcode¬O©ñ¦b­þ­ÓÀÉ®×?¡]µ{¦¡©ñ¦boutstanding payments.xlsm³o­ÓÀÉ®×,¥Îpayment report 2012ùØ­±ªº¸ê®Æ¨Ó¹ï¤ñoutstanding payments.xlsm³o­ÓÀɮתº¤º®e¡C¡^
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")
     §ï¥Î§¹¾ã¸ô®|¬Ý¬Ý¡]¬O¤£¬OWith C:\user\destop\Workbooks.Open("outstanding payments.xlsm").Sheets("outstanding payments")?)
4.  ·f°t°Ï°ìÅܼƵøµ¡&§Y®É¹Bºâµøµ¡ , debug·|§ó¦³®Ä²v¡]¬O¦b­þ­Ó¦ì¸m¡H¡^

¥Ñ©ó§Ú¹ï»yªk¹B¥Î¤£¤Ó¼ô±x¡A©Ò¥H´Nºâ©ú¥Õ­ì²z¡A¦ý¬O¤£ª¾¹D«ç¼Ë¥Îµ{¦¡»yªkªí¹F¡A©Ò¥H¸g±`·Q¤£³q¡C
¤ñ¦p­n¹ï¤ñ¨â­ÓÀɮתº¸ê®Æ¡AA.xlsx ©MB.xlsx
µ{¦¡¬O©ñ¦bA.xlsx ùØ­±
¦P®É­nª¾¹DA.xlsx ©MB.xlsxªº³Ì«á¤@µ§À³¸Ó¦p¦ó¼g
sub ex()
Dim i as integer
Dim j as integer

workbooks(A.xlsx ).Worksheets("Sheet1").Range("A1").Select
ActiveCell.End(xlDown).Select
     i = ActiveCell.Row

fs = "Y:\2012\shipment 2012\B.xlsx"
Set wb = Workbooks.Open(fs)
wb.Worksheets("Sheet1").Range("A1").Select
ActiveCell.End(xlDown).Select
     j = ActiveCell.Row

¥t¥~¤@­Ó¬ODim ªº¹B¥Î

³Ì«á¬O
¦p¦óÅý¹q¸£ÃѧO¨º¥y»yªk¬O¨Ï¥Î¨º­Óexcelªº¸ê®Æ

³o¤TÂI¦pªG©ú¥Õ¥Îªk¡A¨º§Ú´N©ú¥Õ¦p¦ó¸Ñ¨M³o­Ó°ÝÃD¡C

stillfish00

¦^´_ 21# 198188
1. µ{¦¡©ñ¦boutstanding payments.xlsm³o­ÓÀÉ®×
     ªí¥Ü  °õ¦æ®Éoutstanding payments.xlsm¤w¸g¶}±Ò
     ¨º»ò  26¦æ ¤S¥ÎWorkbooks.Open¦A¶}±Ò¤@¦¸´N¨S¦³¥²­n°Õ
3.  §Ú­ì¥»ªº·N«ä¬O§ï¦¨With Workbooks.Open("C:\user\destop\outstanding payments.xlsm").Sheets("outstanding payments")
      ¦ý¬JµM¨S¥²­n¦A¶}±Ò¤@¦¸ ,
      §ï¬°With Workbooks("outstanding payments.xlsm").Sheets("outstanding payments")
      ©Î¬Ù²¤¬°With Sheets("outstanding payments")´N¦n¤F

¦P®É­nª¾¹DA.xlsx ©MB.xlsxªº³Ì«á¤@µ§À³¸Ó¦p¦ó¼g?
      ¼gªk«Ü¦hºØ , ¥uÁ|¨Ò¨ä¤¤¤@ºØ
      Àɮפw¶}±Òªº±¡ªp¤UA.xlsxªº³Ì«á¤@µ§
      With Workbooks("A.xlsx" ).Worksheets("Sheet1")    «ü©w"A.xlsx"¤u§@諸"Sheet1"¤u§@ªí
          Msgbox .Range("A" &  .rows.count).end(xlup).value
     End With
      A©MB¼gªk¬O¤@¼Ò¤@¼Ë , ¥u¬O­Y§Aµ{¦¡¬O¼g¦b A.xlsx ®É
      Workbooks("A.xlsx" ).Worksheets("Sheet1")  ¥i¥H¬Ù²¤¦¨  Worksheets("Sheet1")
      Workbooks("B.xlsx" ).Worksheets("Sheet1")  «h¤£¯à¬Ù²¤

¦p¦óÅý¹q¸£ÃѧO¨º¥y»yªk¬O¨Ï¥Î¨º­Óexcelªº¸ê®Æ
      »yªk«eªºWorkbooks("A.xlsx" )´N¬O«ü©w¨º­ÓEXCEL
      ¦p¦P§Aµ{¦¡14¦æ¼gªºWb¤]¬O«ü©w¸ÓEXCELªü

GBKEE

¦^´_ 21# 198188

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim i As Integer, j As Integer, fs As String
   
4.     Dim Sh(1 To 2) As Worksheet
   
5.     Set Sh(1) = Workbooks("A.xlsx").Worksheets("Sheet1")
   
6.     i = Sh(1).Range("A1").End(xlDown).Row
   
7.     Debug.Print i '¦L¦C¦b §Y®É¹Bºâµøµ¡
   
8.     fs = "Y:\2012\shipment 2012\B.xlsx"
   
9.     Set Sh(2) = Workbooks.Open(fs).Worksheets("Sheet1")
   
10.     j = Sh(2).Range("A1").End(xlDown).Row
    
11.     Debug.Print j     '¦L¦C¦b §Y®É¹Bºâµøµ¡
    
12. End Sub

½Æ»s¥N½X

¤U¸ü (90.75 KB)

2012-12-12 11:07

198188

¦^´_ 22# stillfish00

§Ú¤U­±ªº¼gªk¹ï¶Ü¡H
­Yµ{¦¡¬O¼g¦b A.xlsx ®É
Sub EX()
Dim i as integer
Dim j as integer
Worksheets("Sheet1")   
    i =  .Range("A" &  .rows.count).end(xlup).value    'iµ¥©óA.xlsx AÄ檺³Ì«á¤@µ§

With Workbooks("B.xlsx" ).Worksheets("Sheet1")   
  j = .Range("A" &  .rows.count).end(xlup).value      'jµ¥©óA.xlsx AÄ檺³Ì«á¤@µ§
      End With
End Sub


¥t¥~¦pªG¬O³Ì«á¤@µ§¥[1¬O¤£¬O³o¼Ë¼g
Sub EX()
Dim i as integer
Dim j as integer
Worksheets("Sheet1")   
    i =  .Range("A" &  .rows.count).end(xlup).value  + 1  'iµ¥©óA.xlsx AÄ檺³Ì«á¤@µ§¥[¤@

With Workbooks("B.xlsx" ).Worksheets("Sheet1")   
  j = .Range("A" &  .rows.count).end(xlup).value  + 1    'jµ¥©óA.xlsx AÄ檺³Ì«á¤@µ§¥[¤@
      End With
End Sub

stillfish00

¦^´_ 24# 198188
¤£¬O , À³¸Ó¬O
    i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).value  + 1
©Î¼g¦¨
   with Worksheets("Sheet1")   
      i =  .Range("A" &  .rows.count).end(xlup).value  + 1
   end with   


¥t¥~ .value  ¬OÀx¦s®æ¤ºªº­È
.row¤~¬O¦C¸¹(²Ä´X¦C)

198188

¦^´_ 25# stillfish00



  i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).value  + 1 ¬O«ü³Ì«á¤@µ§ªºÀx¦s®æ­È¥[1 ¡]¤ñ¦pA100¬O³Ì«á¤@µ§¡A¦ÓA100ªº­È¬O3¡A¨º»ò i = 4)
  i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).row  + 1 ¬O«ü³Ì«á¤@µ§¨º¦C¥[1 ¡]¤ñ¦pA100¬O³Ì«á¤@µ§¡A¨º»ò i = 101)

½Ð°Ý¬O¤£¬O³o­Ó·N«ä¡H

stillfish00

¦^´_ 26# 198188
¹ïªº

198188

¦^´_ 27# stillfish00

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.     Dim i As Integer
    
11.     Dim j As Integer
    
12.     Dim k As Integer
    
13.     i = Worksheets("outstanding payments").Range("A" & Worksheets("outstanding payments").Rows.Count).End(xlUp).Row
    
14.     fs = "C:\Documents and Settings\USER\®à­±\payment report 2012.xlsx"
    
15.     Set Wb = Workbooks.Open(fs)
    
16.    
    
17.     With Worksheets("New form of payment report")
    
18.       j = Worksheets("New form of payment report").Range("E" & Worksheets("New form of payment report").Rows.Count).End(xlUp).Row
    
19.     End With
    
20.    
    
21.     Do
    
22.         
    
23.     If Wb.Worksheets("New form of payment report").Range("k" & j).Value = Date And Wb.Worksheets("New form of payment report").Range("h" & j).Value >= 0.95 Then
    
24.    
    
25.     If IsError(Application.VLookup(Wb.Worksheets("New form of payment report").Range("B" & j).Value, Worksheets("outstanding payments").Range("A:A"), 1, False)) Then
    
26.    
    
27.     Worksheets("outstanding payments").Range("A" & i + 1) = Wb.Worksheets("New form of payment report").Range("B" & j).Value
    
28.     Worksheets("outstanding payments").Range("F" & i + 1) = Wb.Worksheets("New form of payment report").Range("H" & j).Value
    
29.     End If
    
30.      j = j - 1
    
31. 
    
32.      i = i + 1
    
33.     End If
    
34.     Loop While j = 1
    
35.    
    
36.    Wb.Close 0
    
37. End Sub

½Æ»s¥N½X

½Ð°Ý¤U­±³o¥y­þ¸Ì¥X¿ù¤F¡H °õ¦æ¶¥¬q¿ù»~¡¥9¡¦¡G°}¦C¯Á¤Þ¶W¥X½d³ò
If IsError(Application.VLookup(Wb.Worksheets("New form of payment report").Range("B" & j).Value, Worksheets("outstanding payments").Range("A:A"), 1, False)) Then

stillfish00

¦^´_ 28# 198188
³Ì²³æªº¤èªk´N¬O15¦æ©³¤U¥[¤WThisWorkbook.Activate ,
©Î§A³£§ï¦¨¼g ThisWorkBook.Worksheets("New form of payment report") ¤]¥i¥H
¦]¬° Workbooks.Open ·|§ïÅÜ·í«e§@¥Î¤¤ªº¬¡­¶Ã¯
§Ú¼g¤Fcode´ú¸Õ

1. 'Codes In a.xlsm
   
2. Sub test()    Dim wb, fstr As String
   
3.     fstr = "C:/b.xlsm"
   
4.    
   
5.     Set wb = Workbooks.Open(fstr)  
   
6.     Debug.Print ThisWorkbook.Name         'will print a.xlsm
   
7.     Debug.Print ActiveWorkbook.Name     'will print b.xlsm
   
8.     Debug.Print Sheets.Parent.Name           'will print b.xlsm
   
9.     wb.Close 0
   
10. End Sub

½Æ»s¥N½X

§Úµoı§Ú«e­±(#22)»¡ªº¦n¹³¤]¤£§¹¥þ¥¿½T
¤£¬O¥HCode¼g¦b­þ¨Ó°Ï§O¯à¤£¯à¬Ù²¤
À³¸Ó§ó¥¿¬°:
¨Ï¥ÎWorkSheets/Sheets»yªk«e­±¨S«ü©w¬¡­¶Ã¯®É ,
·|¦Û°Ê¨Ï¥Î·í®ÉActiveªº¬¡­¶Ã¯©³¤UªºWorkSheet

198188

[/code][b]¦^´_ [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=48297&ptid=8531]29#[/url] [i]stillfish00[/i] [/b]
[code]Option Explicit

Sub ex()

    Dim FRng As Range, Wb As Workbook

    Dim A As Range, Rng As Range

    Dim fs As String, xi As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    i = Worksheets("outstanding payments").Range("A" & Worksheets("outstanding payments").Rows.Count).End(xlUp).Row
    fs = "C:\Users\patrick.HKG\Desktop\payment report 2012.xlsx"
    Set Wb = Workbooks.Open(fs)
   
   
   
    With Worksheets("New form of payment report")
      j = Worksheets("New form of payment report").Range("E" & Worksheets("New form of payment report").Rows.Count).End(xlUp).Row
   
    End With
   
    Do
   
    ThisWorkbook.Activate
   
    If Wb.Worksheets("New form of payment report").Range("k" & j).Value = Date And Wb.Worksheets("New form of payment report").Range("h" & j).Value >= 0.95 Then
   
    If IsError(Application.VLookup(Wb.Worksheets("New form of payment report").Range("B" & j).Value, Worksheets("outstanding payments").Range("A:A"), 1, False)) Then
   
    Worksheets("outstanding payments").Range("A" & i + 1) = Wb.Worksheets("New form of payment report").Range("B" & j).Value
    Worksheets("outstanding payments").Range("F" & i + 1) = Wb.Worksheets("New form of payment report").Range("K" & j).Value
    End If
    i = i + 1
    End If
    j = j - 1
   
   Loop While j = 2
   
   
   Wb.Close 0
End Sub


¥i¥Hvlookup ¥X¨Ó¤F¡A¦ý¬O¥u¦³³Ì«á¤@­Ó¡H
DO
Loop While
³o¥y¬O¤£¬O§Ú¼g¿ù¤F¡H