ELSE following elseif

[Puffin617]

I want to check my column headings to see whether it contains certain pieces of text or match entries in an array BUT if neither of my checks are true, then it must perform the statement after ELSE (moving to next column)

Currently I get a "Else without IF" error

If ActiveCell.Text = "#N/A" Then ActiveCell.EntireColumn.Delete Shift:=xlToLeft
        ElseIf Application.Match(ActiveCell.Text, AnalyteArray, 0) > 0 Then FirstAnalyteColumn = ActiveCell.Column
        Else
            Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
        End If

 

[VoG]

Try

If ActiveCell.Text = "#N/A" Then
    ActiveCell.EntireColumn.Delete Shift:=xlToLeft
ElseIf Application.Match(ActiveCell.Text, AnalyteArray, 0) > 0 Then
    FirstAnalyteColumn = ActiveCell.Column
Else
    Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
End If

 

[Norie]

You are actually missing an If.

I think it might actually be worth looking at your logic as well.

eg Do you even need any ElseIfs?

If you do want the offset to happen every time just remove it from the Ifs completely.

Also, if this code is meant to delete columns you should loop from right to left.

 

[BarCodeWiz]

It gives you the error because it treats this line as a separate statement, separate from the rest of the code:

If ActiveCell.Text = "#N/A" Then ActiveCell.EntireColumn.Delete Shift:=xlToLeft

Just like VoG suggested, move anything after the "Then" onto a separate line.

 

[Norie]

You can loop from right to left using something like this.

I've used a dummy array of 3 values.

Dim rng As Range
Dim I As Long
Dim AnalyteArray
Dim FirstAnalyteColumn As Long
Dim res
 
    AnalyteArray = Array("Field1", "Field2", "Field3")
 
    For I = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1

        Set rng = Cells(1, I)

        If rng.Text = "#N/A#" Then
 
            rng.EntireColumn.Delete Shift:=xlToLeft

        Else

            res = Application.Match(rng.Text, AnalyteArray, 0)

            If Not IsError(res) Then

                FirstAnalyteColumn = rng.Column

            End If
 
        End If
 
    Next I