• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

VBA Nested For loop not executing properly

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
gwarcher
Asked:
gwarcher
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
>> 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
 
gwarcherAuthor Commented:
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
Independent Software Vendors: 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!

 
gwarcherAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
gwarcherAuthor Commented:
still exiting the loop :(
0
 
zorvek (Kevin Jones)ConsultantCommented:
How many cells in column "A" are filled in? What is the name of the sheet containing the email addresses?

Kevin
0
 
gwarcherAuthor Commented:
"Sheet1" and there are 1571 rows
0
 
zorvek (Kevin Jones)ConsultantCommented:
And how is myEmailArray defined?

Kevin
0
 
gwarcherAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
The code works perfectly in my workbook. I defined myEmailArray as Variant and loaded it like so:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Are there any duplicates?

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

Kevin
0
 
gwarcherAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
gwarcherAuthor Commented:
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
 
gwarcherAuthor Commented:
please ignore some of the comments, like i said i carried this over from another application.
0
 
gwarcherAuthor Commented:
I don't get it, I just ran my original application that I created this from and it ran fine
0
 
gwarcherAuthor Commented:
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
 
aikimarkCommented:
@gwarcher

please post your workbook
0
 
zorvek (Kevin Jones)ConsultantCommented:
Or close the question.
0
 
gwarcherAuthor Commented:
Thanks for the help, but  the loop began executing properly when I retyped the code from scratch.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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