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
– GSerg, Commented Jun 14, 2021 at 11:27
There is no need to use Sheets("Tab").Activate here, you can just delete this operation - nothing will change — Алексей Р
– Алексей Р, Commented Jun 14, 2021 at 12:19

Qualia Communications · Accepted Answer · 2021-06-14 12:02:38Z

1

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

answered Jun 14, 2021 at 12:02

Qualia Communications

7158 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GSerg Over a year ago

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.

stasser Over a year ago

This works, but my data amount is too big and it stops Excel , works fine only for a very small amount of data.

Алексей Р · Accepted Answer · 2021-06-14 12:16:48Z

1

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

answered Jun 14, 2021 at 12:16

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Nested loop 'FOR' - Excel VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related