vba 循环遍历列，如果某个值则递增

vba loop through column and if certain value then increment

提问by Christine

I have a column C filled with empty cells or cells that contain value "OK". I need that every cell which contains text "OK" will be change to incremented value 1,2,3 etc. total 2642 cells

我有一列 C 填充了空单元格或包含值“OK”的单元格。我需要每个包含文本“OK”的单元格都将更改为递增值 1、2、3 等。总共 2642 个单元格

   C

- 1 [empty]
- 2 [empty]
- 3 [OK]
- 4 [empty]
- 5 [OK]
- 6 [empty]

Need to look like: C

需要看起来像：C

- 1 [empty]
- 2 [empty]
- 3 [1]
- 4 [empty]
- 5 [2]
- 6 [empty]

Basically i need to autoincrement but when filter data autoincrement doesn't work

基本上我需要自动增量但是当过滤数据自动增量不起作用时

Code i use autoincrements all values by not display value on the the cell that contain word "OK" like this:

我使用的代码通过不在包含单词“OK”的单元格上显示值来自动递增所有值，如下所示：

   C

- 1 [1]
- 2 [2]
- 3 [empty]
- 4 [4]
- 5 [empty]
- 6 [6]

Code:

代码：

    Sub Macro1()
    mynumber = 1
    Dim r As Range, cell As Range
    Set r = Range(Range("C1"), Range("C2642").End(xlDown))
    For Each cell In r
      If cell.Value = OK Then cell.Value = mynumber
       mynumber = mynumber + 1
    Next
    End Sub

回答by CuberChase

Your code is close but the problem you are having is that your cell value is checking OKthinking it is a variable. To avoid this the first thing you want to do is put Option Explicitat the top of each module to tell you when you haven't declared variables.

您的代码很接近，但您遇到的问题是您的单元格值正在检查OK认为它是一个变量。为避免这种情况，您要做的第一件事是Option Explicit在每个模块的顶部放置，以告诉您何时尚未声明变量。

Now all you need to do is make 'OK' a string like "OK". You also need to increment your number inside the Ifstatement otherwise it'll keep incrementing even when you don't want it to.

现在你需要做的就是将 'OK' 变成一个像"OK". 您还需要在If语句中增加您的数字，否则即使您不希望它也会继续增加。

Edit: you can also your range a bit more simply by just defining the cells in the Rangemethod like in this code. Its not as flexible as using Endbut if you have a fixed range will do the trick.

编辑：您也可以更简单地通过在Range方法中定义单元格来更简单地定义此代码中的单元格。它不像使用那样灵活，End但如果你有一个固定的范围就可以了。

Try this code:

试试这个代码：

Option Explicit

Sub Macro1()
    Dim r As Range, cell As Range, mynumber As Long

    Set r = Range("C1:C2642")

    mynumber = 1
    For Each cell In r
        If cell.Value = "OK" Then
            cell.Value = mynumber
            mynumber = mynumber + 1
        End If
    Next
End Sub

回答by Peter Albert

You can do this without a macro:

您可以在没有宏的情况下执行此操作：

1. Change to R1C1 reference style (Filetab->Options->Calculation->R1C1 reference style
2. Select the full column
3. Replace dialog (Ctrl-H)
4. Replace all OKwith =MAX(R1C1:R[-1]C)+1
5. If you wish, copy and paste special the values
6. Uncheck R1C1 reference style

1. 更改为R1C1引用样式（文件标签- >选项- >计算- > R1C1引用样式
2. 选择整列
3. 替换对话框 ( Ctrl- H)
4. 全部替换OK为=MAX(R1C1:R[-1]C)+1
5. 如果你愿意，复制并粘贴特殊的值
6. 取消选中 R1C1 参考样式

Alternatively, you can also do this without R1C1 style by using an Autofilter:

或者，您也可以使用自动过滤器在没有 R1C1 样式的情况下执行此操作：

1. Apply an AutoFilter to your column (Ctrl-Shift-L)
2. Filter for OK
3. Select all filtered OKs
4. Enter the following formula: =MAX($A$1:$A2)+1- enter it with Ctrl-Enter. You need to slightly adjust the formula - replace $A$1with the first row your data start (or the header row) - and $A2with the cell above your active cell- even if it's hidden!
5. Remove the AutoFilter (Ctrl-Shift-L) and copy/paste special values if you wish

1. 将自动筛选器应用于您的列 ( Ctrl- Shift- L)
2. 筛选 OK
3. 选择所有过滤的OKs
4. 输入以下公式：=MAX($A$1:$A2)+1- 输入Ctrl- Enter。你需要稍微调整配方-替换$A$1从第一行数据开始（或标题行） -并$A2与您的活动单元格上方的单元格-即使它是隐藏的！
5. 删除自动过滤器 ( Ctrl- Shift- L) 并根据需要复制/粘贴特殊值