Subscript out of Range Run-time error 9

manthony

New Member
Joined
Dec 5, 2016
Messages
40
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 ?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,486
Office Version
  1. 365
Platform
  1. Windows
I think you have some issues with your sheet and range references. If you want to use the named range, do so like this:
Code:
    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
 

manthony

New Member
Joined
Dec 5, 2016
Messages
40
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,712
Members
409,785
Latest member
lalz1205

This Week's Hot Topics

Top