CODEWITHSUNDEEP
excelvba
LearnForever
LearnForever

Reputation: 185

VBA looping multiple conditions (AND, OR)

I am trying to check multiple conditions and if they are OK, just proceed. Otherwise there should be a message. The macro is run but it doesn't capture errors (e.g. No fund # in column -2) and does not provide with error message as expected. What is wrong?

Sub CheckFundsInISAccounts()

'Version 9 change.

Dim c As Range
Dim lstRng As Range
Dim LastRow As Integer

Application.ScreenUpdating = False

Worksheets("DataFile").Activate
Range("U2").Activate

LastRow = Cells(Rows.count, "A").End(xlUp).row

Set lstRng = Range("U2", Range("U65536").End(xlUp))


For Each c In lstRng
    If c.Value > 29999 And c.Offset(0, -2).Value = 10 Or 11 Or 12 Or 20 Or 45 Or 60 Or 70 Then
        c.Offset(1, 0).Select

    Else

      MsgBox ("NOT every IS account has a Fund assigned to it. Double-check it")

    End If

Next c


Columns("A:W").Select
Selection.EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 4412

Answers (2)

guitarthrower
guitarthrower

Reputation: 5834

There is a difference in result depending on use of parenthesis

If c.Value > 29999 _
And c.Offset(0, -2).Value = 10 _
Or c.Offset(0, -2).Value = 11 _
Or c.Offset(0, -2).Value = 12 _
Or c.Offset(0, -2).Value = 20 _
Or c.Offset(0, -2).Value = 45 _
Or c.Offset(0, -2).Value = 60 _
Or c.Offset(0, -2).Value = 70 Then

Will have different results than the following (which may be what you are looking for)

If c.Value > 29999 _
And (c.Offset(0, -2).Value = 10 _
    Or c.Offset(0, -2).Value = 11 _
    Or c.Offset(0, -2).Value = 12 _
    Or c.Offset(0, -2).Value = 20 _
    Or c.Offset(0, -2).Value = 45 _
    Or c.Offset(0, -2).Value = 60 _
    Or c.Offset(0, -2).Value = 70) Then

Upvotes: 0

Andrew
Andrew

Reputation: 5093

c.Offset(0, -2).Value = 10 Or 11 Or 12 Or 20 Or 45 Or 60 Or 70 Then is not proper code. You need to do each test individually:

c.Offset(0, -2).Value = 10 Or c.Offset(0, -2).Value = 11 Or c.Offset(0, -2).Value = 12 Or c.Offset(0, -2).Value = 20 Or c.Offset(0, -2).Value = 45 Or c.Offset(0, -2).Value = 60 Or c.Offset(0, -2).Value = 70 Then

Of course there are more convenient ways, such as putting your valid values in a list and checking if that list contains c.Offset(0, -2).Value.

Upvotes: 1

Related Questions