How to looping rows and then columns in Excel

user_8302849 0
Viewed 69

I have a table as shown below (B1:L7) Where A1 is lookup values , column B is the header , columns C to L are the data Picture showing the table to be searched of loop

Here is what I intend to do with the macro:

  1. Loop the column B using the lookup values in A1
  2. Once match, in example below, B6 shows matching values to A1 , the values of first 10 values (C to L) (i.e:row 6) are looped to show the values

I have been rig up some macro as followed which it works correctly on step 1

Sub Looping_Click()
'Search columns
Dim c As Range
'Search rows
Dim r As Range
Dim i As Integer

For Each r In Range("B:B")
    If r.Value = Range("A1").Value Then
        MsgBox "Found values at " & r.Address
        
        For Each c In r.Columns
            i = 1
            Do While i <= 10
            MsgBox "Values is " & c.Value
            i = i + 1
            Loop
            
        Next c
    End If
Next r
End Sub

But upon reaching step 2, it somehow repeat showing the B6 values 10 times instead of displaying C A C T C A A T C C

Error Values shows 10 times

vba excel
2 Answers

Here is a solution to your task:

Sub Looping_Click()
'Search columns
Dim c As Range
'Search rows
Dim r As Range

For Each r In Range(Range("B1"), Range("B1").End(xlDown))
    If r.Value = Range("A1").Value Then
        MsgBox "Found values at " & r.Address
        
        For Each c In Range(r.Offset(0, 1), r.Offset(0, 10))
            MsgBox "Values is " & c.Value
        Next c
    End If
Next r
End Sub

I mainly changed following things compared to your version:

  1. Changed For Each r In Range("B:B") to For Each r In Range(Range("B1"), Range("B1").End(xlDown)). Like this the loop will not be carried out over all rows of column B.
  2. Changed For Each c In r.Columns to For Each c In Range(r.Offset(0, 1), r.Offset(0, 10)). This was the key to the unexpected result. Before, you were looping ten times over the cell that you had found. Thanks to the offset it will loop through the desired range.
user_149546520

you checked column b For Each r In Range("B:B") to find interesting for you value in A1.

macro find this value on range B6 and the you want to show msgbox 10 times because For Each c In r.Columns there is only one range in column B:B. (B6)

you need change it to

For i = 3 To 13
   MsgBox Cells(r.Row, i)
Next i

full working code

Sub Looping_Click()
'Search columns
Dim c As Range
'Search rows
Dim r As Range
Dim i As Integer

For Each r In Range("B:B")
    If r.Value = "" Then Exit For
    If r.Value = Range("A1").Value Then
        MsgBox "Found values at " & r.Address
        For i = 3 To 13
            MsgBox Cells(r.Row, i)
        Next i
    End If
Next r
End Sub

i add exit loop while value on b is nothing

user_146247980
Related