View Single Post
 
Old 12-05-2016, 09:20 PM
H28Sailor H28Sailor is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: Mar 2013
Posts: 55
H28Sailor is on a distinguished road
Default VBA Routine debug question

I have the following 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 bold, italic line 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
Reply With Quote