CODEWITHSUNDEEP
excelvba
Meursault
Meursault

Reputation: 123

Loop for cell comparison in IF-THEN statement

I need to compare each cell from range src.Range("A1:A" & 10) with cells in range src3.Range("A1:A" & 3) and proceed only if src cell is not equal to any of src3 cells. It works if I manually list each comparison (as comment - what I have to get eventually. It works but there will be much greater range for comparison). But it does not when I try to loop it. I cannot separateIF from THEN. And I cannot find alternative.

  For temprow = 1 To rngSelectionTable.Rows.Count                         
    tempselected = rngSelectionTable(temprow, 2).Value                      
    Crit = rngSelectionTable(temprow, 5).Value                              

    If tempselected = True Then

    For Each r In src.Range("A1:A" & 10)                

         'If r <> 0 _
          And r <> src3.Range("A1") _
          And r <> src3.Range("A2") _
          And r <> src3.Range("A3") _
          Then myFinalResult = r

        For Each comparisonRange In src3.Range("A1:A" & 3) 'does not work
          If r <> 0 And r <> comparisonRange 'does not work
              Next comparisonRange 'does not work
          Then myFinalResult = r 'does not work

        'rest of the code below
             If myFinalResult = Crit Then                                 
                 If CopyRange Is Nothing Then                        
                     Set CopyRange = r.EntireRow                 
                           Else
                     Set CopyRange = Union(CopyRange, r.EntireRow) 
                 End If
             End If   
       Next r
    End If
  Next temprow

Upvotes: 0

Views: 61

Answers (1)

iDevlop
iDevlop

Reputation: 25272

Something like this should work: 

Sub test()
    Dim r As Range, rng10 As Range, rng3 As Range
    Set rng10 = Sheet1.Range("a1:a10")
    Set rng3 = Sheet2.Range("$a$1:$a$3")

    For Each r In rng10
        If Application.WorksheetFunction.CountIf(rng3, r) > 0 Then
            'proceed here
        End If
    Next r
End Sub

Upvotes: 1

Related Questions