如何在Excel中循环行和列 [英] How to looping rows and then columns in Excel

问题描述

我有一个如下所示的表(B1:L7)其中A1是查询值，B列是标题，C到L列是数据

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

这是我要对宏进行的处理:

Here is what I intend to do with the macro:

1. 使用A1中的查找值环绕B列
2. 一旦匹配，在下面的示例中，B6将显示与A1的匹配值，然后将前10个值(C到L)(即第6行)的值循环显示

我已经按照以下步骤组装了一些宏，该宏在第1步可以正常工作

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

但是在到达步骤2时，它以某种方式重复显示B6值10次，而不是显示C A C T C A A T C C

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

推荐答案

以下是您任务的解决方案:

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. 针对每个范围内的r("B:B")更改为针对范围内的每个(code(Range("B1"))，Range("B1").结束(xlDown)).像这样，循环不会在 B 列的所有行上进行.
2. 将每个c列中的更改为每个c范围中的(code.r.Offset(0，1)，r.Offset(0，10)).这是意外结果的关键.以前，您在找到的单元上循环了十次.感谢 offset ，它将在所需范围内循环.

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.

这篇关于如何在Excel中循环行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！