Error 9: Subscript Out of Range

billyheanue

Board Regular
Joined
Jul 13, 2015
Messages
109
Whats good homies,

I am a excel noob and I get this error 9 when I try to run this. Since it doesnt specify what line is actually wrong, Im wondering if any of the VB Guru's can point me in the right direction in terms of whats wrong with this code? Very confused!:mad:

Code:
Private Sub CommandButton8_Click()


Dim x As Integer
Dim y As Integer
Dim colindexval As Double
Dim resizeval As Double


resizeval = Sheet1.Cells(19, 12).Value
colindexval = Sheet1.Cells(16, 12).Value
Sheet9.Range(x, y).Resize(resizeval, 1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)"
    
End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

revcanon

Board Regular
Joined
Mar 26, 2015
Messages
173
EDIT: I take back my original comment. I have learned differently.

BUMP....
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,210
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This:

Sheet9.Range(x, y) is not a valid range. If you meant Sheet9.Cells(x,y) that's a problem too, because x and y haven't been initialized, both are 0, and Cells(0,0) is not a valid cell address.
 

revcanon

Board Regular
Joined
Mar 26, 2015
Messages
173
You do not have x and y defined here. The subscript error is happening on this line:

Code:
Sheet9.Range([B]x, y[/B]).Resize(resizeval, 1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)"

Make sure you Define x & y, that should resolve that error.

But after that you will receive a different error.

and change that line to:

Code:
Sheets("Sheet9").[B]Cells[/B](x, y).Resize(resizeval, 1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)"

Hopefully that works for you.

Test on a copy!






Looks like Joe beat me to it!
 

dcunningham

Board Regular
Joined
Jul 14, 2015
Messages
58

ADVERTISEMENT

Also an Excel noob. I'm having the same problem, but I'm using a different type of code. I'm just trying to copy a range of values to another sheet which I thought would be fairly simple. Here's my code:

Code:
Sub Copy_range()

Worksheets("Technician Report Summary").Range("G2:G8561").Copy


Worksheets("Summary Print").Range("C10:C8569").PasteSpecial


End Sub

When I run it I get Run-time error '9': Subscript out of range. The cells where I'm pasting the range to are merged while the range that I'm copying from aren't, so I thought that that may be the issue so I un-merged them to see and no, it didn't fix the problem. I still get the same error.

Any ideas?

Regards,

Dan
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try
Code:
With Worksheets("Technician Report Summary").Range("G2:G8561")
    .Copy Worksheets("Summary Print").Range("C10")
End With
 

dcunningham

Board Regular
Joined
Jul 14, 2015
Messages
58

ADVERTISEMENT

Hi Mark,

I tried your code but still got the same message :(. I'm not sure what's wrong.

Thanks,

Dan
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
I tried your code but still got the same message :(. I'm not sure what's wrong.
Are you absolutely sure the sheet names you used in your code match the names on the sheet tabs exactly? Make sure the names in the tabs do not have a leading or trailing space in them.
 

dcunningham

Board Regular
Joined
Jul 14, 2015
Messages
58
The names match up. I just tried the code in another workbook with the same sort of set up and it worked fine. I'm so confused! Could there be something wrong with my workbook or something?

Thanks for the help,

Dan
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
The names match up. I just tried the code in another workbook with the same sort of set up and it worked fine. I'm so confused! Could there be something wrong with my workbook or something?
I guess your workbook could be corrupted, but I really do not know for sure. What I do know is I see nothing wrong with the line of code you posted (nor the shorter version of it that MARK858 posted)... the error you reported, for the line of code you posted, would usually come from a misspelling of the sheet name (hence my original question).
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top