[發問] 急！請問高人可以幫忙解決我的問題？

198188

桌面.rar (1004.63 KB)

下載次數: 4

2012-11-16 22:47

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

回復 1# 198188
試試  Set FRng = wb.Sheets("New form of payment report").Range("B:B").Find(A, lookat:=xlWhole, SearchDirection:=xlPrevious)

198188

回復 2# stillfish00

[attach]13168[/attach] Outstanding Payments.rar (665.87 KB)

下載次數: 3

2012-11-17 10:47

  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

Outstanding Payments.rar (665.87 KB)

下載次數: 1

2012-11-17 10:53

payment report 2012.rar (412.31 KB)

下載次數: 1

2012-11-17 10:53

回復 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

回復 4# 198188

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

For Each A In .Range(.[A2], .Range("A1").End(xlDown))
[A3055]以下的都不會跑到

198188

回復 5# stillfish00


    謝謝~原來是這裏出現問題！另外請問如何讓它辨認儲存格內是否日期還是文字？

kimbal

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