VBA Loop an array and reset

Question

I want to check if my data is within an array and if so it should write it in a new cell.

My problem:

If two entries are the same and follow each other my if statement wont find it in the for loop because my loop doesn't reset.

How can I fix this?

Sub Tester()

    Dim ArtikelZgm As Variant
    ArtikelZgm = Worksheets("Artikel").Range("A1").CurrentRegion
    Dim a As Long
    For a = LBound(ArtikelZgm, 1) To UBound(ArtikelZgm, 1)
        For b = 2 To 100
            If ArtikelZgm(a, 3) = Worksheets("Bestellung Lidl").Cells(b, "F").Value Then

                Worksheets("Bestellung Lidl").Cells(b, "H").Value = ArtikelZgm(a, 1)
                Worksheets("Bestellung Lidl").Cells(b, "I").Value = ArtikelZgm(a, 5)
                b = b + 1

            End If
        Next b
    Next a

End Sub

Does this answer your question? [Check if a value is in an array or not with Excel VBA](https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba) — Foxfire And Burns And Burns, Apr 05 '22 at 09:39
It would be helpful specify the structure of both sheet data in a more detailed way for better understanding - by **editing** the original question @Soujiro2k — T.M., Apr 05 '22 at 14:03
`b = b + 1` should not be in your `For b = 2 To 100` loop... — Tim Williams, Apr 05 '22 at 16:19

score 0 · Accepted Answer · answered Apr 05 '22 at 16:28

0

I would use Match instead of the nested loop:

Sub Tester()

    Dim ArtikelZgm As Variant, m
    Dim b As Long, wsBL As Worksheet, wsA As Worksheet
    
    Set wsA = Worksheets("Artikel")
    Set wsBL = Worksheets("Bestellung Lidl")
    
    ArtikelZgm = wsA.Range("A1").CurrentRegion.Value
    
    For b = 2 To 100
        'using Match is faster than a nested loop
        m = Application.Match(wsBL.Cells(b, "F").Value, wsA.Columns("C"), 0)
        If Not IsError(m) Then 'got a match if `m` is not an error value
            wsBL.Cells(b, "H").Value = ArtikelZgm(m, 1)
            wsBL.Cells(b, "I").Value = ArtikelZgm(m, 5)
        End If
    Next b
End Sub

answered Apr 05 '22 at 16:28

Tim Williams

154,628
8
97
125

is there a way to match the criteria in multiple columns? – Soujiro2k Apr 29 '22 at 08:26
Not easily using Match - you can use a regular loop and check each cell/value: if you don't have a large amount of data the performance should be OK. Like your posted code (without the `b = b+1`) but with more checks in that inner `If` – Tim Williams Apr 29 '22 at 15:52

VBA Loop an array and reset

1 Answers1