CODEWITHSUNDEEP
excelvba
stasser
stasser

Reputation: 123

Nested loop 'FOR' - Excel VBA

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)

enter image description here

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

Upvotes: 0

Views: 973

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

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

Upvotes: 1

Qualia Communications
Qualia Communications

Reputation: 715

  • 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

Upvotes: 1

Related Questions