Nested loop 'FOR' - Excel VBA

Question

can you see where is the issue with nested loop in VBA Excel:

What I am trying to do is to check data (different range and also some duplicate data may occur)

Dan - Sheet with pattern data Tab - Sheet with data that requires additional ID information when Name is same. To check it I select one cell in Dan and proof it with entire column from pattern Tab sheet

Code:

Sub MatchName()
Dim n1 As Long
Dim n2 As Long

 Dim LastRowcheck1 As Long
 Dim LastRowcheck2 As Long
 
LastRowcheck1 = Sheets("Tab").Range("C" & Rows.Count).End(xlUp).Row
LastRowcheck2 = Sheets("Dan").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Tab").Activate
 For n1 = 2 To LastRowcheck1
   For n2 = 2 To LastRowcheck2
        If Sheets("Tab").Cells(n1, 1) = Sheets("Dan").Cells(n2, 1) Then
        Sheets("Tab").Cells(n1, 6) = Sheets("Dan").Cells(n2, 1)
        Sheets("Tab").Cells(n1, 30) = Sheets("Dan").Cells(n2, 2)
    Next n2
  Else
     Next n1
End Sub

No, you don't get to choose with an `If` which loop you are going to `Next`. You have to `Next` both of them. — GSerg, Jun 14 '21 at 11:27
There is no need to use `Sheets("Tab").Activate` here, you can just delete this operation - nothing will change — Алексей Р, Jun 14 '21 at 12:19

score 1 · Accepted Answer · answered Jun 14 '21 at 12:02

Your "If-Then-Else" statement is not nested within the inner "for loop". It need to be closed with "End If" before the "Next n2" statement.
You need to add .Value if you want to check if the content of your two cells is the same
Your LastCheckRow is linked to column C in "Tab", but your are checking for the content of column A in "Tab". Not knowing whether you had data in "A" as well, I changed the column 1 in your check to 3.

Sub MatchName()
Dim n1 As Long
Dim n2 As Long

 Dim LastRowcheck1 As Long
 Dim LastRowcheck2 As Long
 
LastRowcheck1 = Sheets("Tab").Range("C" & Rows.Count).End(xlUp).Row
LastRowcheck2 = Sheets("Dan").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Tab").Activate
 For n1 = 2 To LastRowcheck1
   For n2 = 2 To LastRowcheck2
        Debug.Print "From: " & Sheets("Dan").Cells(n2, 1).Value & " to " & Sheets("Tab").Cells(n1, 3).Value
        If Sheets("Tab").Cells(n1, 3).Value = Sheets("Dan").Cells(n2, 1).Value Then
            Sheets("Tab").Cells(n1, 6).Value = Sheets("Dan").Cells(n2, 1).Value
            Sheets("Tab").Cells(n1, 30).Value = Sheets("Dan").Cells(n2, 2).Value
        End If
    Next n2
  Next n1
End Sub

`You need to add .Value if you want to check if the content` - no, you don't. It's a good thing if you do, but you don't *have* to because `Value` is the [default property](https://stackoverflow.com/a/18051644/11683). — GSerg, Jun 14 '21 at 12:06
This works, but my data amount is too big and it stops Excel , works fine only for a very small amount of data. — stasser, Jun 17 '21 at 11:57

score 1 · Answer 2 · answered Jun 14 '21 at 12:16

A faster way without nested loops:

Sub MatchName2()
    Dim n1 As Long, row As Variant
    
    With Sheets("Tab")
        For n1 = 2 To .Range("A" & .Rows.Count).End(xlUp).row
            row = Application.Match(.Cells(n1, 1), Sheets("Dan").Columns(1), 0)
            If IsNumeric(row) Then
               .Cells(n1, 6) = Sheets("Dan").Cells(row, 1)
               .Cells(n1, 30) = Sheets("Dan").Cells(row, 2)
            End If
        Next n1
    End With
End Sub

Nested loop 'FOR' - Excel VBA

2 Answers2