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

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

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

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

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

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

198188

¦^´_ 23# GBKEE


    ½Ð°Ý With Worksheets("customer").Range(Cells(1, 11), Cells(12, e))³o¥y¬O§_¦³°ÝÃD¡H§Ú·Q±Nrange³]©w¤£©T©w¥ÑA11¶}©l¨ìLÄæ³Ì«á¤@¦Cµe®Ø½u
e = Worksheets("customer").Range("A" & Worksheets("customer").Rows.Count).End(xlUp).Row
With Worksheets("customer").Range(Cells(1, 11), Cells(12, e))
.Borders.LineStyle = 1

.Borders.LineStyle = 1

.Borders.ColorIndex = 0

.BorderAround , 2, 0
End With

198188

¦^´_ 32# GBKEE


    ¥X²{run-time error'9' subscript out of range

198188

¦^´_ 34# GBKEE


     e = Worksheets("customer").Range("A" & Worksheets("customer").Rows.Count).End(xlUp).Row

198188

¦^´_ 36# GBKEE

With Worksheets(customer).Range(Cells(11, 1), Cells(e, 12))
    ²{¦b³o¥y¥X²{RUN-TIME ERROR '1004' APPLICATION-DEFINED OR OBJECT-DEFINED ERROR