198188

回復 20# stillfish00


1. 你的code是放在哪個檔案?（程式放在outstanding payments.xlsm這個檔案,用payment report 2012裏面的資料來對比outstanding payments.xlsm這個檔案的內容。）
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")
     改用完整路徑看看（是不是With C:\user\destop\Workbooks.Open("outstanding payments.xlsm").Sheets("outstanding payments")?)
4.  搭配區域變數視窗&即時運算視窗 , debug會更有效率（是在哪個位置？）

由於我對語法運用不太熟悉，所以就算明白原理，但是不知道怎樣用程式語法表達，所以經常想不通。
比如要對比兩個檔案的資料，A.xlsx 和B.xlsx
程式是放在A.xlsx 裏面
同時要知道A.xlsx 和B.xlsx的最後一筆應該如何寫
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

另外一個是Dim 的運用

最後是
如何讓電腦識別那句語法是使用那個excel的資料

這三點如果明白用法，那我就明白如何解決這個問題。

stillfish00

回復 21# 198188
1. 程式放在outstanding payments.xlsm這個檔案
     表示  執行時outstanding payments.xlsm已經開啟
     那麼  26行 又用Workbooks.Open再開啟一次就沒有必要啦
3.  我原本的意思是改成With Workbooks.Open("C:\user\destop\outstanding payments.xlsm").Sheets("outstanding payments")
      但既然沒必要再開啟一次 ,
      改為With Workbooks("outstanding payments.xlsm").Sheets("outstanding payments")
      或省略為With Sheets("outstanding payments")就好了

同時要知道A.xlsx 和B.xlsx的最後一筆應該如何寫?
      寫法很多種 , 只舉例其中一種
      檔案已開啟的情況下A.xlsx的最後一筆
      With Workbooks("A.xlsx" ).Worksheets("Sheet1")    指定"A.xlsx"工作簿的"Sheet1"工作表
          Msgbox .Range("A" &  .rows.count).end(xlup).value
     End With
      A和B寫法是一模一樣 , 只是若你程式是寫在 A.xlsx 時
      Workbooks("A.xlsx" ).Worksheets("Sheet1")  可以省略成  Worksheets("Sheet1")
      Workbooks("B.xlsx" ).Worksheets("Sheet1")  則不能省略

如何讓電腦識別那句語法是使用那個excel的資料
      語法前的Workbooks("A.xlsx" )就是指定那個EXCEL
      如同你程式14行寫的Wb也是指定該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 '印列在 即時運算視窗
   
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     '印列在 即時運算視窗
    
12. End Sub

複製代碼

下載 (90.75 KB)

2012-12-12 11:07

198188

回復 22# stillfish00

我下面的寫法對嗎？
若程式是寫在 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


另外如果是最後一筆加1是不是這樣寫
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
不是 , 應該是
    i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).value  + 1
或寫成
   with Worksheets("Sheet1")   
      i =  .Range("A" &  .rows.count).end(xlup).value  + 1
   end with   


另外 .value  是儲存格內的值
.row才是列號(第幾列)

198188

回復 25# stillfish00



  i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).value  + 1 是指最後一筆的儲存格值加1 （比如A100是最後一筆，而A100的值是3，那麼 i = 4)
  i =  Worksheets("Sheet1").Range("A" &  Worksheets("Sheet1").rows.count).end(xlup).row  + 1 是指最後一筆那列加1 （比如A100是最後一筆，那麼 i = 101)

請問是不是這個意思？

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

複製代碼

請問下面這句哪裡出錯了？ 執行階段錯誤‘9’：陣列索引超出範圍
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
最簡單的方法就是15行底下加上ThisWorkbook.Activate ,
或你都改成寫 ThisWorkBook.Worksheets("New form of payment report") 也可以
因為 Workbooks.Open 會改變當前作用中的活頁簿
我寫了code測試

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

複製代碼

我發覺我前面(#22)說的好像也不完全正確
不是以Code寫在哪來區別能不能省略
應該更正為:
使用WorkSheets/Sheets語法前面沒指定活頁簿時 ,
會自動使用當時Active的活頁簿底下的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


可以vlookup 出來了，但是只有最後一個？
DO
Loop While
這句是不是我寫錯了？