VBS out of range error

I keep getting a Subscript out of range VBS error 800A0009 on the following script.  Why?
Dim myName 'As String
Dim main_fldr 'As String
Dim subfldr1 'As String
Dim subfldr2 'As String
Dim lastrow 'As Integer
Dim cell 'As Object
Const xlUp = -4162 '(&HFFFFEFBE)
 
 
Set objWshShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
strUserProfile = objWshShell.Environment("PROCESS")("UserProfile")
strNewDirectory = strUserProfile + "\My Documents\Students" 
 
If objfso.FolderExists(strnewdirectory) = False Then
	Set objFolder = objFSO.CreateFolder(strNewDirectory)
End If
 
subfldr1 = inputbox("Do you want to add a subfolder to the childs folder? Make sure you have an excel file called names with the names of all the children in it stored on C:drive before you start.", "Folder name", "add subfolder name here")        'folder1
subfldr2 = inputbox("What is the second foldername? These prompts are for the creation of project folders - for instance I add Assignment and Report as two subfolders. If you add nothing a folder will not be created", "Folder name", "")               'folder2
mynames = strUserProfile & "\My Documents\name.xls"        'location of the excel spreadsheet
worksheetname = "Sheet1"        'name of the worksheet within excel 
 
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(mynames)
objExcel.Visible = False
Set ws = objExcel.activeworkbook.Worksheets(worksheetname)
objExcel.Displayalerts = False
 
 
'find the lastrow on the sheet
lastrow = ws.Range("A65536").End(xlUp).Row
 
'make the folders
For Each cell In ws.Range("A5:A" & lastrow)
    myName = cell.Value & "_" & cell.Offset(0, 1).Value
    'Call MkDir(strNewDirectory & "\") remove this.. its created already
    Call MkDir(strNewDirectory & "\" & myName & "\")
    Call MkDir(strNewDirectory & "\" & myName & "\" & subfldr1 & "\")
    Call MkDir(strNewDirectory & "\" & myName & "\" & subfldr2 & "\")
Next
 
objExcel.Displayalerts = True
objexcel.Quit
wscript.quit
 
 
Function MkDir(strPath)
        Dim strParentPath, objFSO
  Set objFSO = CreateObject("Scripting.FileSystemObject")
        On Error Resume Next
        strParentPath = objFSO.GetParentFolderName(strPath)
  If Not objFSO.FolderExists(strParentPath) Then MkDir strParentPath
        If Not objFSO.FolderExists(strPath) Then objFSO.CreateFolder strPath
        On Error Goto 0 
  MakeDir = objFSO.FolderExists(strPath)
End Function

Open in new window

Dier02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Hi, Dier02.  

What line is the error occuring on?
0
Dier02Author Commented:
Solved the problem but need a different approach.  I want to keep the script as it is but with one change - I want it to add a file called names.xls to the student folder once the Students folder is created and a shortcut created on the desktop to that folder.  I also want it to create a text file named "instructions" with the following text (in that Student Folder) - "Please add your list of names to the Excel file called names in this folder".
0
AngelizedCommented:
At first look you are hardcoding the A65536 as last row and using the cell.Offset(0, 1).Value

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Dier02Author Commented:
OK but how do I do the rest?
0
mohitvashisthaCommented:
Instead of using 65536 why not using

worksheet.UsedRange.Rows.Count

Can You elobrate More on your requirement
0
Dier02Author Commented:
I want to keep the script as it is but with one change - I want it to add a file called names.xls to the student folder once the Students folder is created and a shortcut created on the desktop to that folder.  I also want it to create a text file named "instructions" with the following text (in that Student Folder) - "Please add your list of names to the Excel file called names in this folder".
0
exx1976Commented:
This sounds an awful lot like homework..  Someone posted something VERY similar to this just a few posts away from this one...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.