198188

¦^´_ 34# GBKEE


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

GBKEE

¦^´_ 33# 198188
§A¶·¾Ç·|µ{¦¡½Xªº°»¿ù.
VBAµøµ¡¤¤,·Æ¹«²¾¨ì¦³¿ù»~ªºµ{§Ç,«öF8³v¦æ°õ¦æ¨Ó¬Ý±N,¬O¨º¤@¦æµ{¦¡½Xªºµo¥Í¿ù»~.

¤U¸ü (17.56 KB)

2013-4-17 16:58

198188

¦^´_ 32# GBKEE


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

GBKEE

¦^´_ 31# 198188
Cells(¦C¼Æ, Äæ¼Æ)
   

With Worksheets("customer").Range(Cells(1, 11), Cells( e,12))

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

[/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

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

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

¦^´_ 26# 198188
¹ïªº

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