For each and if loop

Question

I am trying to write a loop which tests each cell in for the number 1, when one is present I want to store the value in the adjacent cell (column A) and transpose all of these in a separate sheet.

However, VBA is not one of my strong points and I'm struggling with getting the first part to work, my code:

Sub test_loop()

Dim Needed_range As Long
Needed_range = Cells(Rows.Count, "B").End(xlUp).Row
    For Each cell In Range(Needed_range)
        If cell = 1 Then
           MsgBox "Yes"
           Exit Sub
        End If
    Next cell

End Sub

Sorry if this is really basic, I don't use VBA often and am going to need to take a refresher to finish this project!

Answer 1

It's fine to use a For Each loop, but you need to construct a Range object to loop through first.

Option Explicit

Sub test_loop()
    Dim neededRange As Range, cell As Range

    'get the range to loop through
    With ThisWorkbook.Worksheets("Sheet1")
        Set neededRange = Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
    End With

    For Each cell In neededRange
        If cell.Value = 1 Then
           cell.Offset(0,-1).Value = cell.Value 'put 1 into column A
           'do something else
        End If
    Next cell
End Sub

• Try not to underscores in variable names. Underscores have a specific meaning elsewhere in VBA
• Don't forget to declare all variables -- I declared cell for you. If you add Option Explicit at the top of your code, you'll be reminded by the IDE
• Rather than just Range("A1") try to fully qualify your ranges. I.e. Workbooks("..").Worksheets("..").Range("A1"). I've done this above in the With statement