CODEWITHSUNDEEP
vbaif-statement
Iaroslav
Iaroslav

Reputation: 3

Complicated if with and, or operators

*I want to create if statement, which would copy a value from one sheet to another if conditions are met. Logical test should be as following: 1. information should always be "not shared" 2. information should be "correct" and "correct" OR "correct" and "wrong" OR "wrong" and "correct" Below you may see the code with which I came so far:

    l = 2
For m = 2 To ElecRow
    If ele.Cells(m, 2) = "Not Shared" And _
           ele.Cells(m, 3) = "Correct" And gc.Cells(m, 3) = "Correct" _
        Or ele.Cells(m, 3) = "Correct" And gc.Cells(m, 3) = "Reading is wrong" _
        Or ele.Cells(m, 3) = "Reading is wrong" And gc.Cells(m, 3) = "Correct" Then
    For i = k + 4 To f + 4
    Selegas.Cells(l, 2).Value = ele.Cells(m, i).Value
    Selegas.Cells(l, 4).Interior.Color = RGB(179, 182, 184)
    l = l + 1
    Next i
    End If
Next m

However, the code is not working. Do you have any suggestions?

Upvotes: 0

Views: 30

Answers (2)

user3598756
user3598756

Reputation: 29421

as per your description your test would be passed if:

  • ele.Cells(m, 2) = "Not Shared"

    AND

  • ele.Cells(m, 3) OR gc.Cells(m, 3) = "Correct"

so you can simply code:

If ele.Cells(m, 2) = "Not Shared" And (ele.Cells(m, 3) = "Correct" Or gc.Cells(m, 3) = "Correct") Then

furthermore, being them all strings you could use the following alternative approach:

Dim corrStrng As String

l = 2
For m = 2 To ElecRow
    corrStrng = "|" & ele.Cells(m, 3) & "|" & gc.Cells(m, 3) & "|" '<--| concatenate two strings into one
    If ele.Cells(m, 2) = "Not Shared" And InStr(corrStrng, "|Correct|") > 0 Then '<--| use Instr() to check if substring "|Correct|" is inside the concatenated word

where you use Instr() function to check if substring "|Correct|" is inside the concatenated word, i.e. if either one of the two parent cells content was "Correct" precisely

Upvotes: 0

Aconcagua
Aconcagua

Reputation: 25518

Problem is that And has higher precedence than or. So (strongly abbreviated for visualisation):

If e2 = ns And e3=c And gc=c Or e3=c And gc=r Or e3=r And gc=c

is equivalent to

If (e2 = ns And e3=c And gc=c) Or (e3=c And gc=r) Or (e3=r And gc=c)
  1. information should always be "not shared"

So you need to enforce this for every case, so you have to break above with additional parentheses:

If e2 = ns And ((e3=c And gc=c) Or (e3=c And gc=r) Or (e3=r And gc=c))

Like your version, omitting surplus parentheses again:

If e2 = ns And (e3=c And gc=c Or e3=c And gc=r Or e3=r And gc=c)

As you got trapped at the precedence point, possibly a good idea to always place parentheses like in third code example. Then you won't miss anything again...

You achieve the same, too, by splitting the If:

If e2 = ns Then
   If e3=c And gc=c Or e3=c And gc=r Or e3=r And gc=c Then

There is an opportunity to optimise and reduce the number of necessary comparisons:

If e2 = ns And (e3=c And (gc=c Or gc=r) Or e3=r And gc=c)

(used the unsplitted if...)

Upvotes: 2

Related Questions