CODEWITHSUNDEEP
excelvba
Matthew F
Matthew F

Reputation: 13

Error when no value found in Range - VBA

I have created VBA code to sort a Stock list, reading the contents of column N and copy the row of any matching item (in this case "KS") into a separate tab.

My code runs correctly, except when no instances can be found in the Stock sheet. "The error is 91 - Object variable or With block variable not set" I have tried variations of If Not with no success. I am sure this is something fundamentally simple, but the answer alludes me.

Suggestions would be appreciated.

Sub KS()
    Dim MyRange, MyRange1 As Range
    Sheets("Stock").Select
    LastRow = Sheets("Stock").Range("K65536").End(xlUp).Row
    Set MyRange = Sheets("Stock").Range("N1:N325" & LastRow)

    For Each c In MyRange
        If c.Value = "KS" Then
            If MyRange1 Is Nothing Then
                Set MyRange1 = c.EntireRow
            Else
                Set MyRange1 = Union(MyRange1, c.EntireRow)
            End If
        End If
    Next

    MyRange1.Select
    Selection.Copy
    Sheets("KS").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Upvotes: 1

Views: 134

Answers (1)

brettdj
brettdj

Reputation: 55692

MyRange is Nothing if there is no matching value so try this

new line

If Not MyRange1 Is Nothing Then MyRange1.Copy Sheets("KS").[a1]

In place of this

remove lines

MyRange1.Select
Selection.Copy
Sheets("KS").Select
Range("A1").Select
ActiveSheet.Paste

Upvotes: 3

Related Questions