Trouble with VBA loop and if/else statements

Question

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

CS. · Accepted Answer · 2022-04-14T12:13:18.070

1

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

edited Apr 14 '22 at 12:13

answered Apr 14 '22 at 12:01

CS.

766
8
24

3

Please, don't put answers on SO for VBA/Excel that uses `Select` and `Selection`. – FunThomas Apr 14 '22 at 12:08
Awesome thanks, it works now. I got rid of the select options too! – Barneschild Apr 14 '22 at 13:08

score 0 · Answer 2 · answered Apr 14 '22 at 12:03

0

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.

answered Apr 14 '22 at 12:03

Spencer Barnes

2,809
1
7
26

Thanks. So with regards to not using select should my first if block look more like this If InStr(1, (Range("A23").Value), "Reason:") > 0 Then Rows("9:11").Delete Shift:=xlUp Rows("14").Delete Shift:=xlUp Rows("16").Delete Shift:=xlUp Rows("18").Delete Shift:=xlUp Range("A7:A18").Copy Range("C6").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 – Barneschild Apr 14 '22 at 12:49
I can't get this write the code nicely...oh the shame – Barneschild Apr 14 '22 at 12:55
Got it all sorted and now the code is half as long! Thanks again for your help – Barneschild Apr 14 '22 at 13:09

Trouble with VBA loop and if/else statements

2 Answers2