CODEWITHSUNDEEP
excelvba
Simon
Simon

Reputation: 1375

Selecting an offset range after finding a cell that matches the value of another cell

I'm having issues with my code as it's returning the error upon running the code "Object variable or With block variable not set". I've tried finding a fix for this and think it has something to do with the Set command but I can't figure it out. I'm not that good with VBA code yet. Still learning. 

Sub FindAndDeleteRange()

Dim rFound As Range
Dim newRange As Range

RowNmbrSumDel = ThisWorkbook.Sheets("Sheet3").Range("W3").Value + 2
Set rFound = Range("A:A").Find(What:=Range("R2").Value, LookIn:=xlValues, lookat:=xlWhole)
Set newRange = Range(rFound, rFound.Offset(RowNmbrSumDel, 10))

If rFound Is Nothing Then
MsgBox "Cannot find that year to delete." & Chr(10) & "Please check you have entered correctly.", , "Error"
ElseIf Not rFound Is Nothing Then
newRange.Select
End If

End Sub

As you can hopefully see, I'm wanting it to find the value from R2 somewhere in column A. If it does then to select the range defined in newRange, but if not to show the error Msgbox. I really don't know how wrong it is. I'm sure I had it somewhat working before I added the msgbox to it but can't remember how it was. Any help is appreciated.

Also: I know its a delete sub. I'm just trying through with selecting. It will be more changed but I need to get past this error first.

Upvotes: 1

Views: 96

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Set newRange = Range(rFound, rFound.Offset(RowNmbrSumDel, 10))

You are getting that error because rFound is nothing and you are trying it use rFound to set newRange

Move the line in the ElseIf section. You will also have to do that for newRange as well.

Also avoid the use of .Select. You may want to see How to avoid using Select in Excel VBA

Is this what you are trying to achieve?

Sub FindAndDeleteRange()
    Dim rFound As Range
    Dim newRange As Range

    RowNmbrSumDel = ThisWorkbook.Sheets("Sheet3").Range("W3").Value + 2

    Set rFound = Range("A:A").Find(What:=Range("R2").Value, _
                 LookIn:=xlValues, lookat:=xlWhole)

    If rFound Is Nothing Then
        MsgBox "Cannot find that year to delete." & Chr(10) & _
        "Please check you have entered correctly.", , "Error"
    ElseIf Not rFound Is Nothing Then
        Set newRange = Range(rFound, rFound.Offset(RowNmbrSumDel, 10))

        '~~> Now Check if newRange is not nothing
        If Not newRange Is Nothing Then
            With newRange
                '~~> Do Something
            End With
        Else
            MsgBox "Cannot find.. your message here..."
        End If
    End If
End Sub

Upvotes: 1

Related Questions