CODEWITHSUNDEEP
vbalistif-statement
horace_vr
horace_vr

Reputation: 3166

VBA If elseif and list of conditions

I have a code for looping through all cells in a range, but I have just found out that the second condition below (cell.row()<>1) should actually be changed into something like cell.row<>1,2,3,4,5 - so a list of exclusions. Is there a quick way of changing this, easier that changing the whole If-Elseif into a Select Case statement?

For Each cell In MyTable
    If cell.Row() = TableFirstRow And cell.Column() = TableFirstColumn Then
        'do stuff
    ElseIf cell.Row() <> 1 And cell.Column() = TableFirstColumn Then
        'do some other stuff
    Elseif...
    End If
Next cell

Upvotes: 1

Views: 222

Answers (3)

user1016274
user1016274

Reputation: 4209

After experimenting with collections (which miss the inclusion operator), and strings (with instr as operator), I finally resort to ranges. The row numbers to exclude can be specified as a list for ease of use: 

Sub TestExclusions()
    Dim cell As Range, ExcludedRows As Range
    Dim ExRowNumbers, i
    ExRowNumbers = Array(1, 3, 5, 6, 7)

    ' initialize exclusions
    Set ExcludedRows = Rows(ExRowNumbers(LBound(ExRowNumbers))) ' first exclusion
    For Each i In ExRowNumbers
        Set ExcludedRows = Union(ExcludedRows, Rows(i))
    Next i


    For Each cell In ActiveSheet.UsedRange
        If Not IsIn(cell, ExcludedRows) Then
            ' ...  do something
        End If
    Next cell
End Sub

Public Function IsIn(rg1 As Range, rg2 As Range) As Boolean
    IsIn = Not (Intersect(rg1, rg2) Is Nothing)
End Function

Of course one could embed the one-liner directly but it's more flexible (and less dauting) to wrap it into a function.

Upvotes: 1

Vincent G
Vincent G

Reputation: 3188

You can define a range with all rows to exclude, and use Application.Intersect to check your condition like:

Dim RowsToExclude as Range
Set RowsToExclude = Union([1],[2], ...)
...
    ElseIf Application.Intersect(cell, RowsToExclude) Is Nothing And cell.Column() = TableFirstColumn Then

or if you rather not use an additional variable:

ElseIf Application.Intersect(cell, Union([1],[2],[3])) Is Nothing And cell.Column() = TableFirstColumn Then

or even:

ElseIf Application.Intersect(cell, [1:6]) Is Nothing And cell.Column() = TableFirstColumn Then

Upvotes: 1

Steve
Steve

Reputation: 1640

Maybe try something like:

For Each cell In MyTable
    If cell.Row() = TableFirstRow And cell.Column() = TableFirstColumn Then
        'do stuff
    ElseIf not "|1|2|3|4|5|" like "*|" & cell.Row() & "|*" And cell.Column() = TableFirstColumn Then
        'do some other stuff
    Elseif...
    End If
Next cell

Upvotes: 1

Related Questions