Error 9: Subscript Out of Range

[billyheanue]

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!

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

 

[revcanon]

EDIT: I take back my original comment. I have learned differently.

BUMP....

 

[JoeMo]

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]

You do not have x and y defined here. The subscript error is happening on this line:

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:

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]

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:

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]

Try

With Worksheets("Technician Report Summary").Range("G2:G8561")
    .Copy Worksheets("Summary Print").Range("C10")
End With

 

[dcunningham]

Hi Mark,

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

Thanks,

Dan

 

[Rick Rothstein]

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]

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]

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).