麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] Error 'Object variable or With block variable not set' [打印本頁]

作者: zxcxz 時間: 2014-2-9 17:26 標題: Error 'Object variable or With block variable not set'

以下程序執行到紅色部份就出現error 'Object variable or With block variable not set'，原因是什麼？

來至微軟官方網：http://msdn.microsoft.com/en-us/library/office/aa195732%28v=office.11%29.aspx
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
      firstAddress = c.Address
      Do
         c.Value = 5
         Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

作者: GBKEE 時間: 2014-2-9 18:00

回復 1# zxcxz

Option Explicit
Sub Ex()
Dim C As Range, firstAddress As String
With Worksheets(1).Range("a1:a500")
.Parent.Select
Set C = .Find(2, LookIn:=xlValues) '[a1:a500] 搜尋 2
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.Value = 5 '一直地將2換置為5,最後再也沒2了
Set C = .FindNext(C)
'搜尋 [a1:a500] 下一個 2
C.Select '沒有2時 C Is Nothing '--> C.Address 錯誤沒有設定變數
Loop While Not C Is Nothing 'And C.Address <> firstAddress
End If
End With
End Sub
Sub Ex1()
Dim C As Range, firstAddress As String
With Worksheets(1).Range("a1:a500")
Set C = .Find(2, LookIn:=xlValues) '[a1:a500] 搜尋 2
If Not C Is Nothing Then
firstAddress = C.Address '
Do
'不執行 C.Value = 5
Set C = .FindNext(C) '搜尋 [a1:a500] 下一個 2
MsgBox C.Address <> firstAddress
'終會回到第一2 -> C.Address = firstAddress '離開迴圈
Loop While C.Address <> firstAddress '條件成立一直執行迴圈
End If
End With
End Sub

複製代碼

作者: stillfish00 時間: 2014-2-9 18:51

回復 1# zxcxz
可改成這樣，因為c為nothing時無法再判斷c.address

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
If c Is Nothing Then Exit Do
Loop While c.Address <> firstAddress
End If
End With

複製代碼

作者: zxcxz 時間: 2014-2-15 09:16

謝謝版主及stillfish00講解

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