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!

CallumDA · Accepted Answer · 2018-03-21 12:43:17Z

3

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

edited Mar 21, 2018 at 12:43

answered Mar 21, 2018 at 8:48

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

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

2 Comments

Shai Rado Over a year ago

cell.Offset(-1,0) is one row above, not one column to the left, it need to be cell.Offset(0, -1)

Maverick Over a year ago

Thank you, much appreciated!

aaa · Accepted Answer · 2018-03-21 08:20:08Z

3

Loop Column B and return Yes with row number, then save value of Column B to Column A

Sub test_loop()

Dim i As Long

    For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row

        If Cells(i, "B").Value = 1 Then
            MsgBox "Yes! Appears at row " & i
            Cells(i,"A").Value = Cells(i, "B").Value
        End If

    Next

End Sub

answered Mar 21, 2018 at 8:20

aaa

8584 gold badges26 silver badges46 bronze badges

1 Comment

Maverick Over a year ago

Thank you, much appreciated!

Shai Rado · Accepted Answer · 2018-03-21 12:35:54Z

Accessing the worksheet object, such as Value = ..something takes the longest for the code to process.

You can avoid that, by using a "helper" range, in the code below I'm using CopyValRng, and every time the cell in column B equals 1 (cell.Value = 1), I'm adding the cell on the left (column A) to that range, using Application.Union.

At the end, I'm just changing the values in the entire range at once using CopyValRng.Value = 1.

Code

Option Explicit

Sub test_loop()

Dim Sht As Worksheet
Dim Needed_range As Range, cell As Range, CopyValRng As Range
Dim LastRow As Long

' set the worksheet object, modify "Sheet1" to your sheet's name
Set Sht = ThisWorkbook.Sheets("Sheet1")
With Sht
    ' get last row in column B
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    ' set the range
    Set Needed_range = .Range("B1:B" & LastRow)

    ' loop through the Range
    For Each cell In Needed_range
        If cell.Value = 1 Then
            If Not CopyValRng Is Nothing Then
                Set CopyValRng = Application.Union(CopyValRng, cell.Offset(0, -1))
            Else
                Set CopyValRng = cell.Offset(0, -1)
            End If
        End If
    Next cell    
End With

' make sure there's at least 1 cell in the range, then put 1's in all the cells in column A at once
If Not CopyValRng Is Nothing Then CopyValRng.Value = 1

End Sub

marc_s · Accepted Answer · 2020-01-08 17:08:21Z

1

(neededRange doesn't stock a range but just the last non empty row in column B) try this code :

    Sub test_loop()

    lastRow = Cells(Rows.Count, "B").End(xlUp).Row

        For i = 1 To lastRow
            If Cells(i, 2).Value = 1 Then
            Cells(i, 2).Select
               MsgBox "Yes"
               Exit Sub
            End If
        Next i

    End Sub

edited Jan 8, 2020 at 17:08

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 21, 2018 at 8:10

LatifaShi

4401 gold badge3 silver badges12 bronze badges

3 Comments

CallumDA Over a year ago

Why did you add Cells(i,2).Select?

LatifaShi Over a year ago

It was just for me to check if it select the right cell .. you can remove this line ..

Maverick Over a year ago

Thank you, much appreciated!

Collectives™ on Stack Overflow

For each and if loop

4 Answers 4

2 Comments

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related