Loop without Do... but i do

Question

I know this has been asked many times but I keep reading and cant get it right. I have included all the necessary End If and the Do is there for my Loop.

My Code

Sub update_names()

Dim EMAIL, NewName As String
Dim i, total As Integer
Dim Search As Range

total = 0
i = 2024
Do While i < 2048
    Sheets("Edit Multiple Subscribers").Select
    EMAIL = Cells(i, 2).Value
    NewName = Cells(i, 1).Value

    Sheets("MASTER").Select
    With Worksheets("MASTER").Cells
        Set Search = .Find(EMAIL, LookIn:=xlValues, After:=ActiveCell)
            If Search Is Nothing Then
                Sheets("Edit Multiple Subscribers").Select
                Cells(i, 2).Interior.Color = RGB(250, 0, 250)
                GoTo Add1
            Else:
                Search.Select
                ActiveCell.Offset(0, -1).Select
                If ActiveCell.Value = NewName Then
                    GoTo Add1
                Else:
                    NewName = ActiveCell.Value
                End If
            End If
        Sheets("Edit Multiple Subscribers").Select

        ActiveSheet.Cells(i, 2).Select
        ActiveCell.Offset(0, -1).Select
        ActiveCell.Value = NewName

        total = total + 1

Add1:         i = i + 1

Loop

MsgBox "Number Of edits: " & total

End Sub

When trying to run it, I get the error 'Loop without Do' even tho the Do is there and I closed the End If — zJuanelo85, Mar 28 '18 at 23:27
@zJuanelo85 read my answer below, a few other things you should consider — Shai Rado, Mar 29 '18 at 05:58

score 1 · Accepted Answer · answered Mar 28 '18 at 23:48

1

You're missing an End With statement, which is equivalent to forgetting an End If-statement as you pointed out yourself.

Side note: I would discourage using GoTo-statements as they make code harder to read and maintain.

answered Mar 28 '18 at 23:48

Stanislas

1,893
1
11
26

Thanks, I put it right before the Loop and I didn't get the error but now I got the following: Run-time error '1004':Application-defined or object-defined error ... and pointed out the 'ActiveCell.Offset(0, -1).Select' in the first Else – zJuanelo85 Mar 28 '18 at 23:54
Make sure that ActiveCrll is on the active Worksheet. Make sure that your Offset doesn't cause impossible vales (use debug).In general avoid using `Select`, see: [How to avoid using select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Stanislas Mar 29 '18 at 00:04

score 1 · Answer 2 · answered Mar 29 '18 at 05:54

The reason for you error already got an answer in @Stanislas answer above.

However, there are a lot other thing you should avoid in your VBA code, like how to avoid using Select, and ActiveCell, instead use fully qualified objects.

Example, in your code you have :

Sheets("Edit Multiple Subscribers").Select
ActiveSheet.Cells(i, 2).Select
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = NewName

This could be replaced with:

Sheets("Edit Multiple Subscribers").Cells(i, 2).Offset(0, -1).Value = NewName

which is not only shorter, but the code will run a lot faster.

Another issue: you have Dim i, total As Integer which means total As Integer but i is actually Variant. You need to define each one explicitly : Dim i As Long, total As Long , I am using Long since it's safer than Integer (can take higher values), and with Windows based x64 systems, they take just as much as memory.

Implementing a few other small modifications, your code could look a little like this:

Option Explicit  ' <--- get used to add this at the top of your code

Sub update_names()

Dim EMAIL As String, NewName As String
Dim i As Long, total As Long
Dim Search As Range

total = 0
i = 2024
Do While i < 2048
    With Sheets("Edit Multiple Subscribers")
        EMAIL = .Cells(i, 2).Value
        NewName = .Cells(i, 1).Value
    End With

    With Worksheets("MASTER")
        Set Search = .Cells.Find(EMAIL, LookIn:=xlValues)
        If Search Is Nothing Then
            Sheets("Edit Multiple Subscribers").Cells(i, 2).Interior.Color = RGB(250, 0, 250)
        Else
            If Search.Offset(0, -1).Value <> NewName Then
                NewName = Search.Value

                Sheets("Edit Multiple Subscribers").Cells(i, 2).Offset(0, -1).Value = NewName
                total = total + 1
            End If
        End If
    End With

    i = i + 1
Loop

MsgBox "Number Of edits: " & total

End Sub

Loop without Do... but i do

2 Answers2