Solved

Trouble with my counter in my loop

Posted on 2013-01-21
13
186 Views
Last Modified: 2013-01-21
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
Comment
Question by:bvanscoy678
  • 7
  • 5
13 Comments
 
LVL 24

Assisted Solution

by:Steve
Steve earned 334 total points
ID: 38802238
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 166 total points
ID: 38802248
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
 
LVL 24

Accepted Solution

by:
Steve earned 334 total points
ID: 38802260
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
 

Author Comment

by:bvanscoy678
ID: 38802300
working through them now. thanks
0
 

Author Comment

by:bvanscoy678
ID: 38802343
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
 
LVL 24

Expert Comment

by:Steve
ID: 38802356
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:bvanscoy678
ID: 38802374
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
 
LVL 24

Expert Comment

by:Steve
ID: 38802385
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
 

Author Comment

by:bvanscoy678
ID: 38802398
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
 

Author Closing Comment

by:bvanscoy678
ID: 38802403
Thank you for the help!
0
 
LVL 24

Expert Comment

by:Steve
ID: 38802458
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
 

Author Comment

by:bvanscoy678
ID: 38802465
I am reading it now. Thanks  for the link.
0
 

Author Comment

by:bvanscoy678
ID: 38802525
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now