Trouble defining start and end rows in my loop

[bvanscoy678]

Hello,


I am getting an error code with this line and not sure why. I am new to loops, so there might be more wrong than I realize.


iRowStart = ImportedSheet.Rows(2) ' This should Row 2 (first row has headers)


My code is looping through the "imported" worksheet and then copying the "approved" (based on the 5th column values) to the "approved worksheet" and the "rejected" rows to the "rejected worksheet". Then I will clear the contents of the imported worksheet (it is a temp worksheet to hold my newly imported data until i separate it).


thanks for any help


https://www.dropbox.com/s/kep1v4yd78rp8ff/vacation%20project%2020%20jan%2013.xls

[pre]

Sub SortApprovedRejected()

Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim iCol As Integer, iRow As Integer, iRowStart As Integer, iRowEnd As Integer, iNextRow As Integer
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range
Dim Approved As String
Dim Rejected As String

Set ApprovedSheet = ThisWorkbook.Worksheets("Approved")
Set ImportedSheet = ThisWorkbook.Worksheets("Imported")
Set RejectedSheet = ThisWorkbook.Worksheets("Rejected")

'//////// My ApprovedWorksheet has an extra column, so this add's the extra column prior to copying
ImportedSheet.Columns(6).Insert

'//////// This will be for my counter
iRowStart = ImportedSheet.Rows(2)  ' This should Row 2 (first row has headers)
iRowEnd = Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = ImportedSheet.Columns(5) 'Check Status of "approved" or "rejected"

For iRow = iRowStart To iRowEnd

If ImportedSheet.Cells(iRow, iCol).Value = Approved Then
'/// move to appropriate sheet
iNextRow = ApprovedSheet.Cells(ApprovedSheet.Rows.Count, 1).End(xlUp).Row + 1
ApprovedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
Else
iNextRow = RejectedSheet.Cells(RejectedSheet.Rows.Count, 1).End(xlUp).Row + 1
RejectedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
End If
Next iRow
End Sub

[/pre]

 

[Marc L]

Hi !   Maybe you have to only try this :

iRowStart = 2   ' This should be Row 2 (first row has headers)


and


iCol = 5   ' Check Status of "approved" or "rejected"

 

 

[bvanscoy678]

Great, that fixed it! Thanks!!