Avoid 2nd loop in VBA

[yxz152830]

Gurus,
i'm comparing 2 sets of data like below:

Sub ajsidjaisdjiadj()
for each cell1 in xxx
for each cell2 in xxx
if cell1 = cell2 then
...
else
...
end if
next cell2
next cell1
End Sub

is there a way to avoid the 2nd loop by some kind of function?
like I can simply just do something like this:

Sub ajsidjaisdjiadj()
for each cell1 in xxx

if cell1.value belongs to 2nd table then
...
else
...
end if
next cell1
End Sub

 

[bebo021999]

Assum cell1 in range A1:C2
lookup range is F1:H2

Sub test()
For Each cell1 In Range("A1:C2")
    If WorksheetFunction.CountIf(Range("F1:H2"), cell1) Then
        MsgBox "OK"
    End If
Next
End Sub

 

[yxz152830]

Assum cell1 in range A1:C2
lookup range is F1:H2

Sub test()
For Each cell1 In Range("A1:C2")
    If WorksheetFunction.CountIf(Range("F1:H2"), cell1) Then
        MsgBox "OK"
    End If
Next
End Sub

gosh this solves my what if condition is not equal to question. BTW though, if I change the condition to does not equal to , and if I want to use only loops, is there a solution to this?

 

[bebo021999]

Like this?

....
 If WorksheetFunction.CountIf(Range("F1:H2"), cell1)=0 Then
...

 

[yxz152830]

Like this?

....
 If WorksheetFunction.CountIf(Range("F1:H2"), cell1)=0 Then
...

I meant like not using any function. I kinda thought of a solution. Just trying to explore different options.

 

[bebo021999]

I meant like not using any function. I kinda thought of a solution. Just trying explore some options.

You can try with "find method".
Im not at PC now, will come back soon.

 

[yxz152830]

You can try with "find method".
Im not at PC now, will come back soon.

I was trying to not use any function just loops, this is what I thought of:

for each cell1 in table1
  for each cell2 in table2
if cell1=cell2 then
a=a+1
end if
next cell2
if a=0 then
...
end if
a = 0
next cell1

i think this can find out all cell1 that does not equal to any cell in table2 without using functions.

 

[DanteAmor]

With dictionary:

Sub test()
  Dim dic As Object
  Dim c As Range
  Set dic = CreateObject("Scripting.Dictionary")
 
  For Each c In Range("B2:B10") 'table 2
    dic(c.Value) = Empty
  Next
 
  For Each c In Range("A2:A10") 'table 1
    If Not dic.exists(c.Value) Then
      c.Interior.Color = vbYellow
      'Or whatever you want to do if the cell in table 1 doesn't exist in table 2
    End If
  Next
End Sub

 

[yxz152830]

With dictionary:

Sub test()
  Dim dic As Object
  Dim c As Range
  Set dic = CreateObject("Scripting.Dictionary")
 
  For Each c In Range("B2:B10") 'table 2
    dic(c.Value) = Empty
  Next
 
  For Each c In Range("A2:A10") 'table 1
    If Not dic.exists(c.Value) Then
      c.Interior.Color = vbYellow
      'Or whatever you want to do if the cell in table 1 doesn't exist in table 2
    End If
  Next
End Sub

im a dictionary lover