Solved

VBA Nested For loop not executing properly

Posted on 2011-09-09
23
543 Views
Last Modified: 2012-05-12
I have a simple application I wrote to remove values on one sheet that are found on another sheet.  For some reason, my For Next loop is not working and I'm not sure why as I have a similarly structured program on another subroutine.  Please let me know what I am doing wrong here.  For reference the Variable count is set above when an array is populated.  For all intensive purposes lets say it is 1000.

The problem I am having is once the inner loop is done executing the code skips the "next i" portion and moves on to the error handling below it.
Dim n As Long, i As Long, arrCount As Integer
Dim Counter As Long
Dim PctDone As Single


arrCount = 0
Counter = 1


For i = 1 To Cells(100, "A").End(xlUp).Row
    iSourceEmail = Range("A" & i).Value
    
    For n = 1 To count
        checkEmail = myEmailArray(n - 1)
        If checkEmail = iSourceEmail Then
            Range("A" & i).Value = ""
            arrCount = arrCount + 1
           
        Else
        End If
    Next n
Next i

If arrCount = 0 Then
    MsgBox ("There are no duplicates to remove")
Else
    MsgBox ("You removed " & CStr(arrCount) & " Numbers from File")
End If

Open in new window

0
Comment
Question by:gwarcher
23 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36513895
Try this:

Public Sub DoWork()

    Dim Row As Long
    Dim Matches As Long
   
    With ThisWorkbook.Worksheets("Sheet1")
        For Row = .Cells(.Rows.Count, "A").End(xlUp).Row To 1
            If IsError(Application.Match(.Cells(Row, "A"), myEmailArray, 0)) Then
            Else
                .Rows(Row).Delete
                Matches = Matches + 1
            End If
        Next Row
    End With

    If Matches = 0 Then
        MsgBox ("There are no duplicates to remove")
    Else
        MsgBox ("You removed " & CStr(Matches) & " Numbers from File")
    End If

End Sub

Kevin
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 36513931
>> For n = 1 To count
What counter is 'count'? It is not initialized so your 'n' loop will only run once.

You can step though your code in Excel with F8 (from VB editor), then you can see what's happening.
0
 

Author Comment

by:gwarcher
ID: 36513958
count is initialized farther up with this:

count = WorksheetFunction.CountA(Range("A1:A106000"))

When I step through, the code executes the inner loop and when it highlights "Next i" it continues on rather than going back to the top.
0
 

Author Comment

by:gwarcher
ID: 36513969
zorvek it does the same thing, it passes the Next Row and moves on to the "There are no duplicates to remove" message box.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36513977
Yikes! Programming error! Fixed:

Public Sub DoWork()

    Dim Row As Long
    Dim Matches As Long
   
    With ThisWorkbook.Worksheets("Sheet1")
        For Row = .Cells(.Rows.Count, "A").End(xlUp).Row To 1 Step -1
            If IsError(Application.Match(.Cells(Row, "A"), myEmailArray, 0)) Then
            Else
                .Rows(Row).Delete
                Matches = Matches + 1
            End If
        Next Row
    End With

    If Matches = 0 Then
        MsgBox ("There are no duplicates to remove")
    Else
        MsgBox ("You removed " & CStr(Matches) & " Numbers from File")
    End If

End Sub

Kevin
0
 

Author Comment

by:gwarcher
ID: 36513997
still exiting the loop :(
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514003
How many cells in column "A" are filled in? What is the name of the sheet containing the email addresses?

Kevin
0
 

Author Comment

by:gwarcher
ID: 36514007
"Sheet1" and there are 1571 rows
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514017
And how is myEmailArray defined?

Kevin
0
 

Author Comment

by:gwarcher
ID: 36514032
Dim myEmailArray() As String
Dim iCount As Long
Dim iEmail As String
    ReDim myEmailArray(0 To count)
   
      'Fill array
    For iCount = 1 To count
        iEmail = Range("A" & iCount).Value
         myEmailArray(iCount - 1) = iEmail
           
    Next iCount
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514036
The code works perfectly in my workbook. I defined myEmailArray as Variant and loaded it like so:

    myEmailArray = Array("A", "D")

Kevin
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514040
Are there any duplicates?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514058
When you execute the code that loads the array, from what sheet are you expecting to load the values?

Kevin
0
 

Author Comment

by:gwarcher
ID: 36514069
From this:

sFileName = GET_FILE()

'load array
Workbooks(sFileName).Activate
count = WorksheetFunction.CountA(Range("A1:A106000"))
    MsgBox ("There are " & CStr(count) & "address in the file")
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36514089
This code is not making much sense. And the fact that you are not qualifying objects such as Cells and Range means that you cannot guarantee the results.

Let's start from the beginning. Where are the email addresses to which you want to compare the list of email addresses on Sheet1?

Kevin
0
 

Author Comment

by:gwarcher
ID: 36514123
I'm just going to dump the whole thing.  I had this working in a similar application and can't figure out why it won't work here.   It is still breaking out of the loop...

I'm sorry, my code is not the cleanest.  I really appreciate the help though
Sub EraseEmails()

'***************************************
'  eraseEmails, used to erease emails from a source spreadsheet
'  Garrett Archer - 9/9/2011
'
'***************************************
Application.ScreenUpdating = False

Dim iSourceEmail As Variant
Dim checkEmail As String
Dim sFileName As String
Dim sWorkbookName As String
Dim count As Long


sWorkbookName = ActiveWorkbook.name



'first we need to open the DNC file and load the array
sFileName = GET_FILE()

'load array
Workbooks(sFileName).Activate
count = WorksheetFunction.CountA(Range("A1:A106000"))
    MsgBox ("There are " & CStr(count) & "address in the file")

Dim myEmailArray() As String
Dim iCount As Long
Dim iEmail As String
    ReDim myEmailArray(0 To count)
    
      'Fill array
    For iCount = 1 To count
        iEmail = Range("A" & iCount).Value
         myEmailArray(iCount - 1) = iEmail
           
    Next iCount

MsgBox ("there are " & iCount & " values in the Array")

're activate source file






'time to check for values in the source file and remove them if they exist in the array

Workbooks(sWorkbookName).Activate

Dim n As Long, i As Long, arrCount As Integer

Dim PctDone As Single

Dim Row As Long
Dim Matches As Long

arrCount = 0


With ThisWorkbook.Worksheets("Sheet1")
    For Row = .Cells(.Rows.count, "A").End(xlUp).Row To 1 Step -1
        If IsError(Application.Match(.Cells(Row, "A"), myEmailArray, 0)) Then
        Else
            .Rows(Row).Delete
            Matches = Matches + 1
        End If
    Next Row
End With

If Matches = 0 Then
    MsgBox ("There are no duplicates to remove")
Else
    MsgBox ("You removed" & CStr(Matches) & " Numbers fromt he file")
End If

'close workbook for testing purposes
Workbooks(sFileName).Close
ActiveWindow.WindowState = xlMaximized


Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:gwarcher
ID: 36514131
please ignore some of the comments, like i said i carried this over from another application.
0
 

Author Comment

by:gwarcher
ID: 36514226
I don't get it, I just ran my original application that I created this from and it ran fine
0
 

Accepted Solution

by:
gwarcher earned 0 total points
ID: 36514288
Have no idea why, but I re coded it word for word from the other application and it started working.  Thanks for the help!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36520595
@gwarcher

please post your workbook
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36520675
Or close the question.
0
 

Author Closing Comment

by:gwarcher
ID: 36890365
Thanks for the help, but  the loop began executing properly when I retyped the code from scratch.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 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