Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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


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

Open in new window

vacation-project-20-jan-13.xls
0
bvanscoy678
Asked:
bvanscoy678
  • 7
  • 5
3 Solutions
 
SteveCommented:
just change it to 2
iRowStart = 2

Open in new window


column too should be:
iCol = 5

Open in new window


I would also suggest dimming row and column numbers as long rather than integer.
0
 
Patrick MatthewsCommented:
iRowStart is declared as an Integer, but ImportedSheet.Rows(2) returns a Range object.

This should work:

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

Open in new window


Also: never use Integer for a row counter.  Once you hit Row 32,768 you will get an overflow.  Just use Long.
0
 
SteveCommented:
Also:

Dim Approved As String
Dim Rejected As String

Open in new window


this will not actually have anything in it.. so "fill" the variable after dimming:
Dim Approved As String: Approved = "Approved"
Dim Rejected As String: Rejected = "Rejected"

Open in new window



it may be easier to drop the variable all together and use:
If ImportedSheet.Cells(iRow, iCol).Value = "Approved" Then

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bvanscoy678Author Commented:
working through them now. thanks
0
 
bvanscoy678Author Commented:
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

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

Open in new window

0
 
SteveCommented:
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.
0
 
bvanscoy678Author Commented:
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"
0
 
SteveCommented:
If you read my first answer you will see I did mention that too.
iCol = ImportedSheet.Columns(8)

Open in new window

should be
iCol = 8

Open in new window

0
 
bvanscoy678Author Commented:
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
0
 
bvanscoy678Author Commented:
Thank you for the help!
0
 
SteveCommented:
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.
0
 
bvanscoy678Author Commented:
I am reading it now. Thanks  for the link.
0
 
bvanscoy678Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now