出現"沒有設定物件變數或With區塊變數"

zamamilo

各位大大好,

想請教大大一下，執行此程式時都會出現

執行階段錯誤’91’

沒有設定物件變數或With區塊變數

程式都會停留在紅色的地方。不知要如何解決


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If ToggleButton3.Value = True Then
    r = Columns("C").Find("小計:", lookat:=xlWhole).Row
    If Target.Address Like "$A$*" And Target.Row >= 16 And Target.Row < r Then
    Application.EnableEvents = False
    For Each A In Target
    Set RNG = Sheets("參照表").Columns("A").Find(A, lookat:=xlWhole)
      If RNG Is Nothing Then MsgBox "索引不存在": Application.EnableEvents = True: Exit Sub
      A.Offset(, 2) = RNG.Offset(, 1): A.Offset(, 3) = RNG.Offset(, 7)
      A.Offset(, 7) = RNG.Offset(, 3): A.Offset(, 8) = RNG.Offset(, 4)
      A.Offset(, 9) = RNG.Offset(, 6): A.Offset(, 10) = RNG.Offset(, 5)
      A.Offset(, 12) = RNG.Offset(, 2)
    Next
    ToggleButton1.Caption = "名稱+規格"
    ToggleButton2.Caption = "廠牌+型號"
    Application.EnableEvents = True
    End If
Else
    r = Columns("C").Find("小計:", lookat:=xlWhole).Row
   If Target.Address Like "$A$*" And Target.Row >= 16 And Target.Row < r Then
    Application.EnableEvents = False
    For Each A In Target
    Set RNG = Sheets("參照表").Columns("A").Find(A, lookat:=xlWhole)
      If RNG Is Nothing Then MsgBox "索引不存在": Application.EnableEvents = True: Exit Sub
      'A.Offset(, 3) = RNG.Offset(, 7)
      A.Offset(, 7) = RNG.Offset(, 3): A.Offset(, 8) = RNG.Offset(, 4)
      A.Offset(, 9) = RNG.Offset(, 6): A.Offset(, 10) = RNG.Offset(, 5)
      A.Offset(, 12) = RNG.Offset(, 2)
    Next
    ToggleButton1.Caption = "名稱+規格"
    ToggleButton2.Caption = "廠牌+型號"
    Application.EnableEvents = True
    End If
End If
End Sub

GBKEE

回復 1# zamamilo
沒有設定物件變數或With區塊變數   :物件變數沒設定好
r = Columns("C").Find("小計:", lookat:=xlWhole).Row   沒找到 "小計" 會有錯誤
改成:

1. If Not Columns("C").Find("小計:", lookat:=xlWhole) Is Nothing Then
   
2.         r = Columns("C").Find("小計:", lookat:=xlWhole).Row
   
3.     Else
   
4.         r = 1  
   
5.     End If

複製代碼