CODEWITHSUNDEEP
excelvbarangeselection
Andrea
Andrea

Reputation: 335

VBA Object variable not set while working with Selection and Ranges

I'm trying to split lines that contain some value in Excel VBA. The problem is not in splitting and copying rows (I'm not there yet!), but in method 'Application.Intersect'. I think I am passing 2 Ranges but the program crashes with the error "Object variable or with block variable not set". When I print them I see something like "$A$2 $C$D false" depending on wath I have selected... What am I doing wrong?

I have this code, 

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

Solution Here Just replace this

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

with that

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

Upvotes: 0

Views: 1815

Answers (1)

Alex
Alex

Reputation: 1642

You have to assign an object to the intersect first before check if it's nothing: 

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

Upvotes: 3

Related Questions