使用选区和范围时未设置VBA对象变量

Question

我试图在Excel VBA中分割包含某些值的行。问题不在于分割和复制行（我还没有！），但是在方法'Application.Intersect'中。我想我传递了2个范围，但程序崩溃，出现错误“Object variable or block variable not set”。 当我打印他们时，我看到像“$ A $ 2 $ C $ D false”取决于我选择的wath ... 我在做什么错了？

我有这样的代码，

Sub SplitRows() 
Dim LastRow As Long, _ 
WS1 As Worksheet, WS2 As Worksheet, _ 
i As Long, j As Integer, ii As Long, X, Y, _ 
MySelection As Range 

Set WS1 = Sheets("Foglio1") 
Set WS2 = Sheets("Foglio2") 
LastRow = Range("A" & Rows.Count).End(xlUp).Row 

With WS1 
    .Range(.Cells(1, 1), .Cells(1, Columns.Count)).Copy 
End With 

With WS2 
    .Cells(1, 1).PasteSpecial 
End With 

Application.CutCopyMode = False 

For i = 2 To LastRow 
    Dim A As Range, SplitSize As Long 

    For ii = 1 To Columns.Count 
     Set A = WS1.Cells(i, ii) 
     Set MySelection = Selection 
     MsgBox A.Address & " " & MySelection.Address & " " & (Application Is Nothing) 

     If Not (Application.Intersect(A, MySelection)) Is Nothing Then 
      SplitSize = UBound(Split(WS1.Cells(i, ii).Value, ",")) 
      Exit For 
     End If 
    Next ii 
MsgBox "SplitSize is" & SplitSize  
Next i 

End Sub 

这里的解决方案 只需更换这

If Not (Application.Intersect(A, MySelection)) Is Nothing Then 

与

If Not (Application.Intersect(A, MySelection) Is Nothing) Then 

Answer 1

你必须先分配对象的交叉检查之前是否没有任何内容：

Set isect = Application.Intersect(A, MySelection) 
    If Not isect Is Nothing Then