MsgBox doesn't go away

[kitkatjam]

I have a macro that looks up a number and copies the information if it's an old account.
The first msgbox displays perfectly; it pops up, I hit "ok" and it continues the code and moves to the next iteration. The next message box, however, continues to pop up when I hit "ok" and I don't know why it won't move to the next iteration. The layout looks the same to me, the iterative piece is on the outside of both "End If" statements, right before End Sub, so it seems as though it should move through correctly.

Do While Cells(i, 1).Value <> ""    
    If IP.Range("B" & i) = "New" Then
        MsgBox "Account is 'new'. Please enter account details."
    Else
    
        Set Num = IP.Range("A" & i)
        Num.Copy
        OP.Activate
        x = WorksheetFunction.CountIf(Range("C:C"), Num.Value)  
        If x = 0 Then
            MsgBox "Number " & Num & "was not found. Please check the number and try again."
        Else

I'm not sure if this is clear, but any help would be greatly appreciated because I'm not sure what I'm doing wrong!
Thanks!

 

[RoryA]

Can you post the rest of the code please?

 

[kitkatjam]

Cvg.ActivateRange("A4").Select
Rows(ActiveCell.row & ":" & Rows.Count).Delete


IP.Activate
Do While Cells(i, 1).Value <> ""   
    If IP.Range("B" & i) = "New" Then
        MsgBox "Account is 'new'. Please enter account details."
    Else
    
        Set Num = IP.Range("A" & i)
        Num.Copy
        OP.Activate
        x = WorksheetFunction.CountIf(Range("C:C"), Num.Value) 
        If x = 0 Then
            MsgBox "Number " & Num & "was not found. Please check the number and try again."
        Else
            row = Application.WorksheetFunction.Match(Num, Range("C:C"), 0)  
            r = row + x - 1 'the row where the number last appears
            On Error GoTo 0
                If row > 0 Then
                Range("A" & row & ":AE" & r).Copy
                End If
            Cvg.Activate
            Range("A" & j).PasteSpecial xlPasteValues
        
            s = j + x 'one row below last line for total
            t = s - 1 'last line
            Set inc = Cvg.Range("AC" & j & ":AC" & t)
            Set exc = Cvg.Range("AD" & j & ":AD" & t)
            Range("Q" & s).Value = Application.WorksheetFunction.SumIfs(Range("Q" & j & ":Q" & t), inc, "Include", exc, "") 
            Range("R" & s).Value = Application.WorksheetFunction.SumIfs(Range("R" & j & ":R" & t), inc, "Include", exc, "") 
            Range("S" & s).Value = Application.WorksheetFunction.SumIfs(Range("S" & j & ":S" & t), inc, "Include", exc, "")
            Range("T" & s).Value = Application.WorksheetFunction.SumIfs(Range("T" & j & ":T" & t), inc, "Include", exc, "")
            Range("U" & s).Value = Application.WorksheetFunction.SumIfs(Range("U" & j & ":U" & t), inc, "Include", exc, "")

        
            Range("A" & t).Copy
            IP.Activate
            Range("C" & i).PasteSpecial xlPasteValues   
            Cvg.Activate
            Range("F" & t).Copy
            IP.Activate
            Range("D" & i).PasteSpecial xlPasteValues  
            Cvg.Activate
            Range("I" & t).Copy
            IP.Activate
            Range("E" & i).PasteSpecial xlPasteValues  




            j = s + 2
    
        End If
         
    End If
    
i = i + 1
Loop
    
        
        
End Sub

 

[RoryA]

I meant the full code. What routine is this and where is the code located? (which module)

 

[kitkatjam]

I meant the full code. What routine is this and where is the code located? (which module)

I'm sorry, I'm not sure I understand... what is a routine?

 

[RoryA]

A routine is the generic name for a Sub or Function.

 

[rlv01]

How do you know it is not moving on to the next iteration? I suspect that you do not have a msgbox problem, you have an x = 0 problem. If the 2nd msgbox keeps popping up repeatedly

        If x = 0 Then
            MsgBox "Number " & Num & "was not found. Please check the number and try again."
        Else

it more likely because x = 0 for each iteration. You can test this by using the debugger to single step through the code.

 

[kitkatjam]

How do you know it is not moving on to the next iteration? I suspect that you do not have a msgbox problem, you have an x = 0 problem. If the 2nd msgbox keeps popping up repeatedly

        If x = 0 Then
            MsgBox "Number " & Num & "was not found. Please check the number and try again."
        Else

it more likely because x = 0 for each iteration. You can test this by using the debugger to single step through the code.

When I stepped through the code, it worked perfectly. It read that x=0, popped up the message box, I hit "OK" and it jumped to End If, then iterated; since there was nothing in the next line, it then jumped to exit sub. It worked exactly as I would think it should work, but when I run the code without stepping through it piecemeal, it wouldn't stop popping up with the MsgBox.

 

[kitkatjam]

Oh I think I see what you mean - I think I misspoke. It doesn't look like it is actually exiting the sub. My IP.Activate needed to be hit again, so it was looking in the wrong tab to see if the line was blank or not, and then just running for probably 20,000 lines until it died out.
THANK YOU SO MUCH! That was amazing.