For each loop does not operate as expected

Question

I have two sheets in excel, one is a board with several cells with numbers inside, and the other is references (that have the numbers in previous board) and i need to write in same line of the references where are the cells located.
image of the first board where are the references
image of the excel sheet that i have to write the location of each reference
my vba code

Example:

The arm8.png is the board and local.png is where i write de localizations of the cells

Option Explicit

Sub ciclo()

    Dim FindString As String
    Dim Rng As Range
    Dim matrixVal As Range

    Set matrixVal = Sheets("Localizações").Range("B1")
    FindString = matrixVal

    For Each Rng In matrixVal

        If Trim(FindString) <> "" Then

            With Sheets("Arm8").Range("A1:J10")

                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

                If Not Rng Is Nothing Then
                    'Application.Goto Rng, False
                    'MsgBox Rng.Column & " - " & Rng.Row
                Else
                    MsgBox "Nothing found"
                End If

            End With

            With Sheets("Localizações")
                .Range("C1:C9").Value = Rng.Column
                .Range("D1:D9").Value = Rng.Row
            End With

        End If

    Next Rng

End Sub

I expected the output in local.png to be column C and D

2 - 9
2 - 7
2 - 8
2 - 4
5 - 4
7 - 4
5 - 9
9 - 7
9 - 0

You have set matrixval, to be one single cell. So your For-Each loop will only run once. — Stavros Jon
– Stavros Jon, Commented May 27, 2019 at 10:15

Stavros Jon · Accepted Answer · 2019-05-27 10:57:35Z

Firstly, as I said in my comment, this:

Set matrixVal = Sheets("Localizações").Range("B1")

sets matrixVal as one single cell (B1 to be precise), so your For-Each loop doesn't have any cells to loop through apart from this single cell, so it will only run once.

Second, the FindString needs to be updated inside the loop, otherwise you'll be searching for the same value over and over.

Finally, you shouldn't update the Rng variable inside the loop because you are already using it to loop through a range. You need a second variable of type Range.

Your code should look like:

 Sub ciclo()

    Dim FindString As String
    Dim Rng As Range
    Dim cell As Range
    Dim matrixVal As Range

    Set matrixVal = ThisWorkbook.Worksheets("Localizacoes").Range("B1:B9")

    For Each cell In matrixVal
        FindString = cell.Value

        If Trim(FindString) <> "" Then

            With ThisWorkbook.Worksheets("Arm8").Range("A1:J10")

                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

                If Not Rng Is Nothing Then
                    With ThisWorkbook.Worksheets("Localizacoes")
                        .Cells(cell.Row, "C").Value = Rng.Column
                        .Cells(cell.Row, "D").Value = Rng.Row
                    End With
                Else
                    MsgBox "Nothing found"
                End If

            End With

        End If

    Next cell

End Sub

Collectives™ on Stack Overflow

For each loop does not operate as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related