How to loop through multiple cell values on VBA?-CodePudding

I'm trying to simplify the following code into one loop only. How can I do that?

 Dim VARIANTE as LONG

 For Each cell In Sheets("Libro2").Range("C1:C30000")

      If cell.Value = Sheets("Libro1").Range("AA1") Then
         VARIANTE = cell.Row
        Sheets("Libro2").Range("Z" & VARIANTE) = 1
    End If
 Next

 For Each cell In Sheets("Libro2").Range("C1:C30000")

      If cell.Value = Sheets("Libro1").Range("AA2") Then
         VARIANTE = cell.Row
        Sheets("Libro2").Range("Z" & VARIANTE) = 1
    End If
 Next

  For Each cell In Sheets("Libro2").Range("C1:C30000")

      If cell.Value = Sheets("Libro1").Range("AA3") Then
         VARIANTE = cell.Row
        Sheets("Libro2").Range("Z" & VARIANTE) = 1
    End If
 Next

[...] 'and so on, and so forth

Here is something I've tried, but it didn't work, of course. Maybe there is a solution out there, but I couldn't find it because of language issues.

  For Each cell In Sheets("Libro2").Range("C1:C30000")

      If cell.Value = Sheets("Libro1").Range("AA1:AA50") Then
         VARIANTE = cell.Row
        Sheets("Libro2").Range("Z" & VARIANTE) = 1
    End If
 Next

CodePudding user response：

Please, try the next code:

Sub testLoopCols()
 Dim sh As Worksheet, shL As Worksheet, rngAA As Range, arrC, arrZ, i As Long, mtch
 
 Set sh = ActiveSheet 'use here the sheet you need
 Set shL = Sheets("Libro1")
 arrC = sh.Range("C1:C30000").value  'place the range in an array for faster iteration
 arrZ = sh.Range("Z1:Z30000").value  'place the range to return (using an array)
 Set rngAA = shL.Range("AA1:AA50")   'the range where to match each cell of C:C range
 For i = 1 To UBound(arrC)
    mtch = Application.match(arrC(i, 1), rngAA, 0) 'match in a range = much faster than in an array...
    If Not IsError(mtch) Then        'if a match exists:
        arrZ(i, 1) = 1               'place 1 in the final array
    End If
 Next i
 'drop the processed array result, at once:
 sh.Range("Z1:Z30000").value = arrZ
 MsgBox "Ready..."
End Sub

CodePudding user response：

This solution is what so brilliantly BigBen, in only one sentence, answered my question inmediately, and which I'm enterily grateful.

  For Each cell In Sheets("Libro2").Range("C1:C30000")
     If WorksheetFunction.CountIf(Sheets("concat").Range("AD20951:AD20956"), cell.Value) > 0 Then
     VARIANTE = cell.Row
     Sheets("Libro2").Range("Z" & VARIANTE) = 1
     End If
  Next