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

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

Collectives™ on Stack Overflow

Loop Without Do Error - Can't Find Solution

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related