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
Solved

VBA Nested For loop not executing properly

Posted on 2011-09-09
23
549 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 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

792 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