CODEWITHSUNDEEP
vbaexcel
Chris Helgeson
Chris Helgeson

Reputation: 23

If multiple conditions match then return true

Hello I am fairly new at VBA. I am working in a large spreadsheet with over 10,000 rows. What I am trying to accomplish is:

  1. First a macro with will look in column E for the value 1

  2. if there is a match it will then look one cell over to the left in the column D for two possible string values.

  3. If both conditions are met for that row then return true in the corresponding G row

This is what I have below and it is not working below and I am running into errors. Any help is appreciated. 

    Sub find_mismatch()
     Dim c As Range
     Dim string1 As String
     Dim string2 As String
     string1 = "Apple"
     string2 = "Orange"

     For Each c In Range("E1:E10138")
         If c.Value = 1 Then
             If ActiveCell.Offset(-1, 0).Value = string1 Or string2 Then
                ActiveCell.Offset(2, 0).Value = "True"
             End If
         End If
     Next
End Sub

Upvotes: 2

Views: 770

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Multiple if conditions always have to be written in full, even if it feels like you're repeating yourself.

Change this:

If ActiveCell.Offset(-1, 0).Value = string1 Or string2 Then
    ActiveCell.Offset(2, 0).Value = "True"
End If

to this:

If c.Offset(0, -1).Value = string1 Or c.Offset(0, -1).Value = string2 Then
    c.Offset(0, 2).Value = "True"
End If

Edit: just realized that the rows and column were also reversed. The syntax for offset is Offset(Rows,Columns). so to go left you put the negative number in the second argument not the first.

Upvotes: 1

Related Questions