[發問] 找了好幾天找不出錯誤,請協助?

本帖最後由 GBKEE 於 2013-9-19 13:14 編輯

我在"main"工作表的A2儲存格(我定義它為product)輸入一個產品代號,則在"main"工作表的range("C2:C20")會自動顯示該產品所需的原材料代號
原理是我將"main"工作表的range("C2:C20")設成陣列函數(=Sample(product)),這個函數會到"material"工作表的A欄去尋找與"main"工作表C2儲存格字串相同的儲存格,當找到後會將對應的"material"工作表的B欄儲存格資料儲存成陣列,最後再回傳給Sample
我偵測的結果,錯誤是在findnext上,當第一次以find找到時,確實是在$A$19儲存格上,但是接下來set rng=.findnext(rng)的結果,卻始rng變成nothing?(正常應該是$A$20才對)

程式碼如下:
Public Function Sample(product As Variant) As Variant
Application.Volatile
Dim oRange As Range
Dim rng As Range
Dim ws As Worksheet
Dim ExitLoop As Boolean
Dim FoundAt As String
Dim temp(18) As Variant
Dim i As Long
Dim MySearch As Variant
On Error GoTo Err
If TypeName(product) = "String" Then
      MySearch = Range(product).Value
ElseIf TypeName(product) = "Range" Then
      MySearch = product.Value
End If
With Sheets("material").Columns("A")
         Set rng = .Find(What:=MySearch)

         If Not rng Is Nothing Then
            FirstAddress = rng.Address
            Do
                  temp(i) = rng.Offset(0, 1).Text
                  Set rng = .FindNext(rng) '函數執行到這裡時,會出錯
                                             '經偵錯,前面第一個find找到的位置$A$19沒錯
                                             '本次的findnext執行後,rng變成nothing
                                             '但正確的位置應該是$A$20
                  'If rng Is Nothing Then Exit Do
                  i = i + 1
            Loop While Not rng Is Nothing And rng.Address <> FirstAddress
         End If
End With
Sample = temp
Set rng = Nothing
Exit Function
Err:
Set rng = Nothing
MsgBox Err.Description
Sample = "N/A"
End Function