?
Solved

VBA Nested For loop not executing properly

Posted on 2011-09-09
23
Medium Priority
?
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
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 46

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

762 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