Trouble with VBA loop and if/else statements-CodePudding

I'm trying to get a loop with multiple if/else statements to work but it keeps saying either I have Ifs with no End Ifs or that I have a Loop with no Do.

Any pointers would be great.

Below is the what I've done so far, please go easy on me I only started trying to write in vba yesterday...

Sub EditTransposeCopy()

Sheets("Altered").Select

Dim count As Long
count = WorksheetFunction.CountA(Range("A6", Range("A6").End(xlDown))) - 1

Do While count > 0
If InStr(1, (Range("A23").Value), "Reason:") > 0 Then
    Rows("9:11").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("14").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("16").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("18").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A7:A18").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("A6:N6").Copy
    Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Altered").Select
    Rows("6:18").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    count = count - 19
Else
    If InStr(1, (Range("A20").Value), "Reason:") > 0 Then
    Rows("9:11").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("14").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("16").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A7:A16").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("A6:L6").Copy
    Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Altered").Select
    Rows("6:16").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    count = count - 16
Else
    If InStr(1, (Range("A17").Value), "Reason:") > 0 Then
    Rows("9:11").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Rows("14").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A7:A14").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    Range("A6:J6").Copy
    Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Altered").Select
    Rows("6:14").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    count = count - 13
Else
    If InStr(1, (Range("A15").Value), "£0.00") > 0 Then
    Sheets("Altered").Select
    Rows("9:11").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A7:A12").Select
    Selection.Copy
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A6:H6").Copy
    Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Altered").Select
    Rows("6:12").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    count = count - 10
Else
    count = count - 10000000
End If
Loop

'
End Sub

Thanks in advance

CodePudding user response：

Welcome to SO, and welcome to VBA!

First up, you should look at how to avoid using select, because although this is how the macro recorder works, it's better practice (less prone to bugs) and more readable if you replace code like

Range("A1").Select
Selection.Copy

with

Range("A1").Copy

Secondly look up the syntax of an if statement - in particular this part about use of Else If will be handy in the above code. Each If requires it's own End If, looks like you missed a couple in your original code.

CodePudding user response：

Use ElseIf or terminate each If block with an End If

Sub EditTransposeCopy()

'...

Do While count > 0
    If InStr(1, (Range("A23").Value), "Reason:") > 0 Then
        '...
    ElseIf InStr(1, (Range("A20").Value), "Reason:") > 0 Then
        '...
    ElseIf InStr(1, (Range("A15").Value), "£0.00") > 0 Then
        '...
    Else
        '...
    End If
Loop

End Sub