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

 

[MARK858]

I'll wait 1/2 hour until I get home on my laptop.

 

[MARK858]

Assuming you are 100% certain that they are the fourteenth and fifteenth sheets what happens with the code below (I would seriously use a copy of your workbook)

Sub Testit()
    Sheets(14).Name = "Technician Report Summary"
    Sheets(15).Name = "Summary Print"
    
    With Worksheets("Technician Report Summary").Range("G2:G8561")
        .Copy Worksheets("Summary Print").Range("C10")
    End With

End Sub

 

[dcunningham]

Hi Mark,

I counted again to be sure and VBA lists the Sheet# next to the renamed sheets too, they're definitely the fourteenth and fifteenth sheets.

I ran the code and still got the Run-time error '1004': Application defined or object-defined error.

Should I scrap this workbook and start again from scratch?

Thanks,

Dan

 

[MARK858]

Are you sure your sheets or workbook aren't protected and your cells are currently unmerged?

 

[dcunningham]

No, they are not protected.

 

[MARK858]

and the answer to the late edit I done

and your cells are currently unmerged?

If the answer is yes upload your file to a free file hosting site like www.Box.com (any sensitive data sanitized)and post a link in the thread.

 

[dcunningham]

By Jove, I think you've got it! I un-merged the cells and bam! it works. Thank you so very much! I was ready to try some percussive persuasion.

Now, why wouldn't it work?

Cheers,

Dan

 

[Rick Rothstein]

By Jove, I think you've got it! I un-merged the cells and bam! it works.

Just so you know, merged cells almost always cause a problem... if you must have them, you should always mention their existence when you ask a question so the people you are asking help from are alerted to their presence.

 

[MARK858]

Now, why wouldn't it work?

If your first code doesn't work (and the code I posted in post #6) but the last code posted does (all with the cells unmerged, which I thought already were due to your statement in post #5 "so I un-merged them to see and no, it didn't fix the problem. I still get the same error") then at least one your sheet names in your tabs wasn't matching the code.

If you mean when you have "merged" cells then that is because they are the work of an evil programmer at Microsoft (I think he also worked on Vista, Windows 8 and Excel 2007) and should be annihilated at the earliest opportunity.

 

[dcunningham]

Oh, OK I think I understand. So it was trying to find that sheet but couldn't because for whatever reason me changing the name in the tab didn't register with VBA. Interesting...

When the code I wrote failed and unmerging didn't work, I assumed that my guess was wrong so I just restored it to its earlier (and preferable formatting-wise) state. Sorry, I should have been clearer.

Thank you very much for your help, it's greatly appreciated.

Regards,

Dan