Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2016, 09:23 PM
H28Sailor H28Sailor is offline VBA routine debug question Windows 7 32bit VBA routine debug question Office 2007
Advanced Beginner
VBA routine debug question
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default VBA routine debug question

I have the following code:-



Code:
Sub CopyRows()
' copy all rows of data in worksheet 'temp' to the appropriate worksheets
    Dim iLooper As Long, NextRow As Long, ws As Worksheet, strSheet As String
    With Worksheets("temp")
        For iLooper = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            If UCase(Trim(.Cells(iLooper, "B").Value)) Like "ENROLLMENT*" Then
                strSheet = "ENROLLMENT"
            ElseIf UCase(Trim(.Cells(iLooper, "B").Value)) Like "WEEK*" Then
                strSheet = .Cells(iLooper, "a").Text
            Else
                strSheet = ""
            End If
            If strSheet <> "" Then
                Set ws = Worksheets(strSheet)
                NextRow = ws.Columns("B").Find("*", , xlValues, , 1, 2).Row + 1
                ws.Rows(NextRow).Insert
                ws.Cells(NextRow, "A").Resize(, 8).Value = _
                    .Cells(iLooper, "A").Resize(, 8).Value
            End If
        Next
    End With
' delete all rows of data in worksheet 'temp'
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
  If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub
When run I get a run-time error '9': Subscript out of range together with the line Set ws = Worksheets(strSheet) above highlighted

The routine processes all the lines as they should be - and so the error occurs at the end of processing and, of course, the lines of data in 'temp' are not erased

Would welcome any comments on how to correct the code..........

Bob M

Last edited by H28Sailor; 12-06-2016 at 02:01 AM. Reason: Add code tags
Reply With Quote
  #2  
Old 12-06-2016, 08:13 AM
NoSparks NoSparks is offline VBA routine debug question Windows 7 64bit VBA routine debug question Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

At the time the procedure faults do you have a sheet with the name in the strSheet variable?
Be sure to check for leading and/or trailing spaces.
Reply With Quote
  #3  
Old 12-06-2016, 10:38 AM
H28Sailor H28Sailor is offline VBA routine debug question Windows 7 32bit VBA routine debug question Office 2007
Advanced Beginner
VBA routine debug question
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default

Nor sure how I identify the value of the strSheet variable at the time it falls over?
Reply With Quote
  #4  
Old 12-06-2016, 11:21 AM
NoSparks NoSparks is offline VBA routine debug question Windows 7 64bit VBA routine debug question Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Mouse over strSheet and VBA should tell you.

Have a look at http://www.cpearson.com/excel/DebuggingVBA.aspx for info regarding debugging code.
Reply With Quote
  #5  
Old 12-06-2016, 11:44 AM
H28Sailor H28Sailor is offline VBA routine debug question Windows 7 32bit VBA routine debug question Office 2007
Advanced Beginner
VBA routine debug question
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default

yes, there are several rows with the value that strSheet has including the final row of data to be processed
Reply With Quote
  #6  
Old 12-06-2016, 12:10 PM
H28Sailor H28Sailor is offline VBA routine debug question Windows 7 32bit VBA routine debug question Office 2007
Advanced Beginner
VBA routine debug question
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default

OK - have realised that I do not have a tab in the workbook with that value
All sorted :0
Bob M
Reply With Quote
  #7  
Old 12-06-2016, 12:23 PM
NoSparks NoSparks is offline VBA routine debug question Windows 7 64bit VBA routine debug question Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Glad you got things rectified.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Routine debug question H28Sailor Excel 1 12-05-2016 10:51 PM
VBA routine debug question Debug error gbaker Excel Programming 5 10-01-2016 10:35 AM
VBA routine debug question automated form filling routine / process Smithy02468 Excel Programming 1 11-08-2014 11:29 PM
VBA routine debug question Data got getting returned from Sub Routine Hwyn Excel Programming 5 01-18-2012 08:02 AM
Macro or routine required evanhughes Outlook 0 11-18-2011 02:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft