Board logo

標題: [發問] 急！請問高人可以幫忙解決我的問題？ [打印本頁]
作者: 198188    時間: 2012-11-16 22:59     標題: 急！請問高人可以幫忙解決我的問題？

[attach]13166[/attach]
Sub HK()

Dim FRng As Range

Dim A As Range, Rng As Range
Dim i As Integer

fs = "C:\Documents and Settings\USER\桌面\payment report 2012.xlsx"

'fs = ThisWorkbook.Path & "\payment report 2012.xlsx"
Set wb = Workbooks.Open(fs)

With ThisWorkbook.Worksheets("2012")

For Each A In .Range(.[A2], .Range("A1").End(xlDown))

   Set FRng = wb.Sheets("New form of payment report").Range("B65536").End(xlUp).Find(A, lookat:=xlWhole)

   If Not FRng Is Nothing Then

      If FRng.Offset(, 6).Value > 0.95  and A.Offset(, 5) ="" 或“文字” Then
          A.Offset(, 5) = FRng.Offset(, 9).Value
         If Rng Is Nothing Then Set Rng = A.Offset(, 5) Else Set Rng = Union(Rng, A.Offset(, 5))
       End If
    End If
      Set FRng = Nothing
Next
End With

wb.Close 0

End Sub

由於PAYMENT REPORT 2012內的SO#有重複，以上的vba是尋找第一個相同的SO#，請問有無方法尋找最後一筆相同的SO＃？
另外在PAYMENT REPORT 2012內找到後，如果paid percentage一欄符合百分比95或以上，以及在OUTSTANDING PAYMENTS內CHINA/HK PAY一欄如果是空格就顯示PAYMENT REPORT 2012內paid date一欄的值。如果在OUTSTANDING PAYMENTS內CHINA/HK PAY一欄是文字就顯示PAYMENT REPORT 2012內paid date一欄的值加原本的文字。但如果是日期就CHINA/HK PAY一欄不變。
作者: stillfish00    時間: 2012-11-17 08:37

本帖最後由 stillfish00 於 2012-11-17 08:40 編輯

回復 1# 198188
試試  Set FRng = wb.Sheets("New form of payment report").Range("B:B").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
作者: 198188    時間: 2012-11-17 10:52

回復 2# stillfish00

[attach]13168[/attach][attach]13169[/attach]
  Sub HK()

Dim FRng As Range

Dim A As Range, Rng As Range
Dim i As Integer

fs = "C:\Users\Desktop\payment report 2012.xlsx"

'fs = ThisWorkbook.Path & "\payment report 2012.xlsx"'肮珨醴?
作者: 198188    時間: 2012-11-17 10:57

[attach]13170[/attach][attach]13171[/attach]回復 3# 198188

Sub HK()
Dim FRng As Range
Dim A As Range, Rng As Range
Dim i As Integer
fs = "C:\Users\Desktop\payment report 2012.xlsx"
'fs = ThisWorkbook.Path & "\payment report 2012.xlsx"'同一目錄時使用
Set wb = Workbooks.Open(fs)
With ThisWorkbook.Worksheets("2012")
For Each A In .Range(.[A2], .Range("A1").End(xlDown))
    Set FRng = wb.Sheets("New form of payment report").Range("B:B").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)
    If Not FRng Is Nothing Then
       If FRng.Offset(, 6).Value >= 0.95  and A.Offset(, 5) = "" Then
          A.Offset(, 5) = FRng.Offset(, 9).Value '讓2012的F欄等於NEW FORM OF PAYMENT REPORT 的K 欄
          If Rng Is Nothing Then Set Rng = A.Offset(, 5) Else Set Rng = Union(Rng, A.Offset(, 5))
       End If
    End If
      Set FRng = Nothing
Next
End With
wb.Close 0
End Sub
謝謝~現在可以找到最後一個相同的資料，但是有些找不到。例如：208935 和 208936 就沒有反映。請問是哪裡出現問題了？
作者: stillfish00    時間: 2012-11-17 11:32

本帖最後由 stillfish00 於 2012-11-17 11:33 編輯

回復 4# 198188

因為工作表 "2012" 的 [A3055] 為空白

For Each A In .Range(.[A2], .Range("A1").End(xlDown))
[A3055]以下的都不會跑到
作者: 198188    時間: 2012-11-17 11:40

回復 5# stillfish00


    謝謝~原來是這裏出現問題！另外請問如何讓它辨認儲存格內是否日期還是文字？
作者: kimbal    時間: 2012-11-17 22:24

回復 6# 198188
是不是日期
    isdate([A1])
是不是數字
   isnumeric([A1])



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)