Loop Without Do Error - Can't Find Solution

Question

I am trying to loop through cells and replace certain numbers with a coded domain number (ie - 6 should be coded as a 2, etc). I have used this exact same method in numerous other places throughout this VBA and it seems to work just fine. This portion is giving the error of "Loop Without Do" even though there is an "End If" statement. I've searched through other questions and answers and can't seem to pinpoint my mistake. Any help would be greatly appreciated!

Sub LateralSizeID()

'Column AO

Dim wb As Workbook
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim nr As Long

Set wb = ThisWorkbook
Set sht1 = wb.Sheets("From AGO")
Set sht2 = wb.Sheets("To MapCall")

nr = 2

Range("AO2").Select

Do Until IsEmpty(ActiveCell)

  If sht2.Cells(nr, "AO").Value = "6" Then
        sht2.Cells(nr, "AO").Value = "2"

  If sht2.Cells(nr, "AO").Value = "4" Then
        sht2.Cells(nr, "AO").Value = "1"

  If sht2.Cells(nr, "AO").Value = "8" Then
        sht2.Cells(nr, "AO").Value = "3"

  Else: sht2.Cells(nr, "AO").Value = "2"

  End If

  nr = nr + 1

ActiveCell.Offset(1, 0).Select

Loop

MainSizeID

End Sub

Try replacing all ifs except the first one by ElseIf and replacing IsEmpty(ActiveCell) by sht2.Cells(nr, "AO").Value = "" — user8608712
– user8608712, Commented Apr 18, 2018 at 13:49
Ahhhhhh that was it!!! I forgot the "ElseIf" and just made it say "If" ! — Rmoore
– Rmoore, Commented Apr 18, 2018 at 13:56

QHarr · Accepted Answer · 2018-04-18 14:00:41Z

3

You need 2 more End Ifs

Option Explicit

Sub LateralSizeID()

    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim nr As Long

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("From AGO")
    Set sht2 = wb.Sheets("To MapCall")

    nr = 2

    Range("AO2").Select

    Do Until IsEmpty(ActiveCell)

        If sht2.Cells(nr, "AO").Value = "6" Then
            sht2.Cells(nr, "AO").Value = "2"

            If sht2.Cells(nr, "AO").Value = "4" Then

                sht2.Cells(nr, "AO").Value = "1"

                If sht2.Cells(nr, "AO").Value = "8" Then
                    sht2.Cells(nr, "AO").Value = "3"

                Else
                   sht2.Cells(nr, "AO").Value = "2"

                End If

                nr = nr + 1

                ActiveCell.Offset(1, 0).Select
            End If
        End If

    Loop

    MainSizeID

End Sub

I think the whole thing could become:

Option Explicit

Public Sub LateralSizeID()

    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim nr As Long, rng As Range

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("From AGO")
    Set sht2 = wb.Sheets("To MapCall")

    With sht2                                    'change as required
        For Each rng In .Range(.Range("AO2"), .Range("AO2").End(xlDown))
            Select Case rng.Value
            Case 4
                rng = 1
            Case 8
                rng = 3
            Case Else
                rng = 2
            End Select
        Next rng

        MainSizeID

    End With
End Sub

edited Apr 18, 2018 at 14:00

answered Apr 18, 2018 at 13:50

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dwirony Over a year ago

Wouldn't he need ElseIfs instead?

Mathieu Guindon Over a year ago

@dwirony arguably should be a Select...Case block ;-)

dwirony Over a year ago

@MathieuGuindon Yes or that!

QHarr Over a year ago

@MathieuGuindon That was what I was typing up

Rmoore Over a year ago

Yes.. the "ElseIf" got me.. I had used "If" multiple times!

Collectives™ on Stack Overflow

Loop Without Do Error - Can't Find Solution

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related