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

Question

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.

Put the line `Set newRange = Range(rFound, rFound.Offset(RowNmbrSumDel, 10))` in the `ElseIf` part of `If rFound Is Nothing Then` and try again... Just before the `newRange.Select` — Siddharth Rout, Sep 06 '19 at 09:26
Ahhh thank you so much. So simple yet went beyond me. Cheers. Do you want to put it as an actual answer so I can close it and give it to you? — Simon, Sep 06 '19 at 09:29

score 1 · Accepted Answer · answered Sep 06 '19 at 09:43

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

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

1 Answers1