Brent
asked on
Trouble with my counter in my loop
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
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
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
vacation-project-20-jan-13.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Barman,
This code worked. I took the suggestion of getting rid of the variable to make it a little less confusing.
I am looking at matthewpatricks suggestion now.
thanks
This code worked. I took the suggestion of getting rid of the variable to make it a little less confusing.
I am looking at matthewpatricks suggestion now.
thanks
Sub SortApprovedRejected()
Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim iCol As Long, iRow As Long, iRowStart As Long, iRowEnd As Long, iNextRow As Long
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range
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 = 2
iRowEnd = Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = 8
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
The suggestion to use
iRowStart = ImportedSheet.Rows(2).Row
is the same as using
iRowStart = 2
There is in reality no difference, I would tend to plump for simplicity.
iRowStart = ImportedSheet.Rows(2).Row
is the same as using
iRowStart = 2
There is in reality no difference, I would tend to plump for simplicity.
ASKER
I replaced the iRowStart and got an error with:
iCol = ImportedSheet.Columns(8) 'Check Status of "approved" or "rejected"
'//////// This will be for my counter
iRowStart = ImportedSheet.Rows(2).Row ' 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(8) 'Check Status of "approved" or "rejected"
iCol = ImportedSheet.Columns(8) 'Check Status of "approved" or "rejected"
'//////// This will be for my counter
iRowStart = ImportedSheet.Rows(2).Row ' 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(8) 'Check Status of "approved" or "rejected"
If you read my first answer you will see I did mention that too.
iCol = ImportedSheet.Columns(8)
should beiCol = 8
ASKER
After working with both of the ideas, I think i will use The Barman's because it makes sense to me as I step through it.
Thank you both for the help. I did not realize I was returning a range object, so I'll need to read up on that before I move on. I have one more step for the loop which is to check for duplicate values (the A column is a unique identifier) prior to copying the row.
I am going to step through this several more times and watch the variables so I make sure I completely get it.
Again, Thanks!
Brent
Thank you both for the help. I did not realize I was returning a range object, so I'll need to read up on that before I move on. I have one more step for the loop which is to check for duplicate values (the A column is a unique identifier) prior to copying the row.
I am going to step through this several more times and watch the variables so I make sure I completely get it.
Again, Thanks!
Brent
ASKER
Thank you for the help!
If planning to work with unique values... please take the time to read MathewsPatrics most excellent article on Dictionaries.
It is really handy for what you are doing.
It may be a tad tough at first, but I assure you it is a great article and a realy useful part of VBA.
If you would like to raise another question on how to apply a Dictionary to your code method for unique values, I would be happy to write the code for you to learn from.
It is really handy for what you are doing.
It may be a tad tough at first, but I assure you it is a great article and a realy useful part of VBA.
If you would like to raise another question on how to apply a Dictionary to your code method for unique values, I would be happy to write the code for you to learn from.
ASKER
I am reading it now. Thanks for the link.
ASKER
I am not sure it would be appropriate to comment on the article here, but thanks for the link. If I read it correctly, I would set up a dictionary, have my unique values stored into an array, then check my new values against the array, looking for duplicate values.
I'll comment on Patrick's post page.
thanks,
Brent
I'll comment on Patrick's post page.
thanks,
Brent
ASKER