vba 遍历单元格并添加到范围

Loop through cells and add to a range

提问by user1067701

How would I loop through cells B1 to J1 and add them to a range if they meet a certain criteria. For example.

如果它们满足特定条件，我将如何遍历单元格 B1 到 J1 并将它们添加到一个范围内。例如。

Dim Range1 As Range
For i = 1 to 9
If Range("A1").Offset(1,i) meets a certain criteria Then
**Add that cell to Range1**
End If
Next i

I'm not sure how to approach the part of adding certain cells to Range1.

我不确定如何处理将某些单元格添加到 Range1 的部分。

Thanks for the help!

谢谢您的帮助！

回答by brettdj

Something like this using Unionto glue together your range

像这样的东西Union用于将您的范围粘合在一起

1. Please note that For eachloops are quicker than a For i = 1 to xapproach

2. You may well be able to use SpecialCellsto determine your new range instantly (e.g. any blanks, any errors, any formulae, etc)

   Sub Test()
     Dim rng1 As Range
     Dim rng2 As Range
     Dim c As Range
     Set rng1 = Range("B1:J1")
   
     For Each c In rng1
       ' Add cells to rng2 if they exceed 10
       If c.Value > 10 Then
           If Not rng2 Is Nothing Then
           ' Add the 2nd, 3rd, 4th etc cell to our new range, rng2
           ' this is the most common outcome so place it first in the IF test (faster coding)
               Set rng2 = Union(rng2, c)
           Else
           ' the first valid cell becomes rng2
               Set rng2 = c
           End If
       End If
     Next
   End Sub
   

1. 请注意，For each循环比For i = 1 to x方法更快

2. 您很可能可以使用SpecialCells立即确定您的新范围（例如任何空白、任何错误、任何公式等）

   Sub Test()
     Dim rng1 As Range
     Dim rng2 As Range
     Dim c As Range
     Set rng1 = Range("B1:J1")
   
     For Each c In rng1
       ' Add cells to rng2 if they exceed 10
       If c.Value > 10 Then
           If Not rng2 Is Nothing Then
           ' Add the 2nd, 3rd, 4th etc cell to our new range, rng2
           ' this is the most common outcome so place it first in the IF test (faster coding)
               Set rng2 = Union(rng2, c)
           Else
           ' the first valid cell becomes rng2
               Set rng2 = c
           End If
       End If
     Next
   End Sub
   

回答by Philip Young

I use this method in immediate mode when I don't want to add code to the sheet.

当我不想向工作表添加代码时，我在立即模式下使用此方法。

strX="": _
For Each cllX in Range( ActiveCell, Cells( Cells.SpecialCells(xlCellTypeLastCell ).Row, ActiveCell.Column) ): _
strX=strX & iif(cllX.text="","",iif(strX="","",",")& cllX.address): _
Next: _
Range(strX).Select

But while that is intuitive, it only works for up to 35 to 50 cells. After that, the VBA returns an error 1004.

虽然这很直观，但它最多只适用于 35 到 50 个细胞。之后，VBA 返回错误 1004。

Run-time error '1004':
Application-defined or object-defined error

It is more robust to use the Unionfunction.

使用Union函数更健壮。

Set rngX=ActiveCell: _
For Each cllX in Range( ActiveCell, Cells( cells.SpecialCells(xlCellTypeLastCell ).Row, ActiveCell.Column) ): _
Set rngX=iif( cllX.text="", rngX, Union(rngX, cllX) ): _
Next: _
rngX.Select

It is so short and intuitive, I just throw it away after each use.

它是如此简短和直观，我每次使用后就把它扔掉。