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-14 12:13:18Z

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, 2022 at 12:13

answered Apr 14, 2022 at 12:01

CS.

7849 silver badges24 bronze badges

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

2 Comments

FunThomas Over a year ago

Please, don't put answers on SO for VBA/Excel that uses Select and Selection.

Barneschild Over a year ago

Awesome thanks, it works now. I got rid of the select options too!

Spencer Barnes · Accepted Answer · 2022-04-14 12:03:03Z

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, 2022 at 12:03

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

3 Comments

Barneschild Over a year ago

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 Over a year ago

I can't get this write the code nicely...oh the shame

Barneschild Over a year ago

Got it all sorted and now the code is half as long! Thanks again for your help

Collectives™ on Stack Overflow

Trouble with VBA loop and if/else statements

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related