在Excel中循环遍历范围的每一行

4 回答

• 128

  Dim a As Range, b As Range
  
  Set a = Selection
  
  For Each b In a.Rows
      MsgBox b.Address
  Next
  

  回复于 2024-05-07T00:44:09+08:00
• 129

  像这样的东西：

  Dim rng As Range
  Dim row As Range
  Dim cell As Range
  
  Set rng = Range("A1:C2")
  
  For Each row In rng.Rows
    For Each cell in row.Cells
      'Do Something
    Next cell
  Next row
  

  回复于 2024-05-07T00:44:09+08:00
• 5

  在循环中，我总是喜欢使用 Cells 类，使用R1C1引用方法，如下所示：

  Cells(rr, col).Formula = ...
  

  这使我能够轻松快速地轻松地在 Range 的单元格上进行操作：

  Dim r As Long
  Dim c As Long
  
  c = GetTargetColumn() ' Or you could just set this manually, like: c = 1
  
  With Sheet1 ' <-- You should always qualify a range with a sheet!
  
      For r = 1 To 10 ' Or 1 To (Ubound(MyListOfStuff) + 1)
  
          ' Here we're looping over all the cells in rows 1 to 10, in Column "c"
          .Cells(r, c).Value = MyListOfStuff(r)
  
          '---- or ----
  
          '...to easily copy from one place to another (even with an offset of rows and columns)
          .Cells(r, c).Value = Sheet2.Cells(r + 3, 17).Value
  
  
      Next r
  
  End With
  

  回复于 2024-05-07T00:44:09+08:00
• 4

  只是偶然发现了这一点，并认为我会建议我的解决方案 . 我通常喜欢使用内置功能将范围分配给多调度数组（我猜它也是我的JS程序员） .

  我经常写这样的代码：

  Sub arrayBuilder()
  
  myarray = Range("A1:D4")
  
  'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensioned
  
  For i = 1 To UBound(myarray)
  
      For j = 1 To UBound(myarray, 2)
  
      Debug.Print (myarray(i, j))
  
      Next j
  
  Next i
  
  End Sub
  

  将范围分配给变量是在VBA中操作数据的一种非常强大的方法 .

  回复于 2024-05-07T00:44:09+08:00