循环通过单元格并添加到一个范围 [英] Loop through cells and add to a range

问题描述

如果循环到单元格B1到J1，并将它们添加到一个范围，如果它们符合一定的标准，我将如何循环。例如

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

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

我不知道如何处理向Range1添加某些单元格的部分。

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

感谢您的帮助！

推荐答案

使用 Union 一起你的范围

1. 请注意，对于每个循环比对于i = 1到x 方法



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

1. Please note that For each loops are quicker than a For i = 1 to x approach

2. You may well be able to use SpecialCells to 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

这篇关于循环通过单元格并添加到一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！