Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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
 



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:46 PM.


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