For each loop output to displayed as list-CodePudding

Sub search()
    Dim cell As Range
    For Each cell In Range("A1:A50")
        If cell.Value = 1 Then
       
        End If
    Next
End Sub

I want to extract the cell address whose value is equal to 1 for range A1:A50

The output should be shown as table in the excel eg in the Range("A1:A50") if the total cell having value equal to 0 is 3 and lies in A5, A11 and A20. I want the output displayed as list in excel cell.

Column A	Column B
A5
A11
A20

CodePudding user response：

Sub nbcnb()
Dim cell As Range
Dim i As Integer
i = -1
For Each cell In Range("A1:A5")
If cell.Value = 1 Then
i = 1   i
Range("A14").Offset(i, 0) = cell.Address
End If
Next
End Sub

CodePudding user response：

Avoiding Loop Counter as iterating through an array turns out to be less efficient this way.

Sub nbcnb()
Dim cell As Range
For Each cell In Range("A1:A5")
If cell.Value = 1 Then
Range("A14").End(xlDown).Offset(1, 0) = cell.Address
End If
Next
End Sub