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

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

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

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ªü

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

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

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

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

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

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