VBA, Loop troubleshooting and Compile Else If error

[chee321]

Hi guys,

This is my code as followed,

Sub Macro3()
' Macro3 Macro


    Sheets("MV filter").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("MV-70%").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
    Sheets("MV-70%").Select
    Range("C4").Select
    Do Until IsEmpty(ActiveCell)
    If Range("C4") <> "" Then
         Sheets("MV-70%").Select
          Range("A4:C4").Select
            Range(Selection, Selection.End(xlDown)).Select
            Application.CutCopyMode = False
            ActiveWorkbook.Worksheets("MV-70%").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("MV-70%").Sort.SortFields.Add Key:=Range("C5:C2674" _
                ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                With ActiveWorkbook.Worksheets("MV-70%").Sort
                .SetRange Range("A4:C2674")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
         Sheets("MV-70%").Select
         Range("A4:C4").Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.Copy
         Sheets("Sheet3").Select
         Range("A1").Select
         Selection.End(xlDown).Select
         ActiveCell.Offset(1, 0).Range("A1").Select
         Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
         False, Transpose:=True
         Sheets("MV-70%").Select
         Columns("C:C").Select
         Selection.Delete Shift:=xlToLeft
         Range("C4").Select
     Else
            Sheets("MV-70%").Select
            Range("A4").Select
            
        End With
      Application.ScreenUpdating = False
End Sub

There is a compile Else If error when I try to loop it. Not sure how to debug.

 

[Fluff]

Hi & welcome to MrExcel.
You have the End With in the wrong place & are missing an End If & Loop.
Try

Sub Macro3()
' Macro3 Macro

Application.ScreenUpdating = False
   Sheets("MV filter").Select
   Range("A1").Select
   Range(Selection, Selection.End(xlToRight)).Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Sheets("MV-70%").Select
   Range("A4").Select
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=True
   
   Sheets("MV-70%").Select
   Range("C4").Select
   Do Until IsEmpty(activecell)
      If Range("C4") <> "" Then
         Sheets("MV-70%").Select
         Range("A4:C4").Select
         Range(Selection, Selection.End(xlDown)).Select
         Application.CutCopyMode = False
         ActiveWorkbook.Worksheets("MV-70%").Sort.SortFields.Clear
         ActiveWorkbook.Worksheets("MV-70%").Sort.SortFields.Add Key:=Range("C5:C2674" _
            ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
         With ActiveWorkbook.Worksheets("MV-70%").Sort
            .SetRange Range("A4:C2674")
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        [COLOR=#ff0000] End With[/COLOR]
         Sheets("MV-70%").Select
         Range("A4:C4").Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.Copy
         Sheets("Sheet3").Select
         Range("A1").Select
         Selection.End(xlDown).Select
         activecell.Offset(1, 0).Range("A1").Select
         Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
         Sheets("MV-70%").Select
         Columns("C:C").Select
         Selection.Delete shift:=xlToLeft
         Range("C4").Select
      Else
         Sheets("MV-70%").Select
         Range("A4").Select
      [COLOR=#ff0000]End If[/COLOR]
   [COLOR=#ff0000]Loop[/COLOR]
   Application.ScreenUpdating = True
   End Sub

 

[chee321]

Omg you are amazing man. Thanks for the help!
Nice community here.

 

[Fluff]

Glad to help & thanks for the feedback