Subscript out of Range Run-time error 9

[manthony]

Hi,

I am getting a script out of range error message.

I am trying to look up the text in cell B4 on Sheet “Specs.” My table array is a named range call “ContinueWithTask”. ContinueWithTask is columns A:X on a sheet called “Task Names”.

If the vlookup finds the cells is empty, I want it to display a messagebox and exit the macro.

On only declaration is Dim ContinueWithTask As Range

Any ideas? My code is below.

Dim ContinueWithTask As Range

If IsEmpty(Application.WorksheetFunction.VLookup(Sheets(Specs!).Range("B4"), ContinueWithTask, 5, False) = True) Then
MsgBox ("Complete. Please review!")
Exit Sub
End If

Thanks for the help ?

 

[VBA Geek]

at first glance it looks like the =TRUE has to be moved outside and what is ContinueWithTask? if its a worksheet range then you need to replace it with sheets("Task Names").range("ContinueWithTask")

 

[Norie]

Try this.

Dim ContinueWithTask As Range
Dim Res As Variant

    Res = Application.VLookup(Sheets("Specs").Range("B4"), Sheets("Task Names").Range("A:X"), 5, 0)
    
    If IsError(Res) Then
        MsgBox ("Complete. Please review!")
        Exit Sub
    End If

 

[Joe4]

I think you have some issues with your sheet and range references. If you want to use the named range, do so like this:

    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Sheets("Specs").Range("B4")
    Set rng2 = Range("ContinueWithTask")
    
    If IsEmpty(Application.WorksheetFunction.VLookup(rng1, rng2, 5, False)) Then
        MsgBox ("Complete. Please review!")
        Exit Sub
    End If

 

[Trebor76]

You could have also easily adapted my Evaluate solution that I posted to this earlier thread by you.

 

[manthony]

Thank you all for the help. VBA Geek you were correct (as were the rest of you). I needed to correct the ContinueWith Task range and move the TRUE. Thank you all so much for the help; it is greatly appreciated.