vba循环通过列，如果某个值然后增加 [英] vba loop through column and if certain value then increment

问题描述

我有一个C列填充空的单元格或包含值OK的单元格。我需要包含文本OK的每个单元格将改变为增量值1,2,3等。总共2642个单元格

  C 
 
  -  1 [空] 
  -  2 [空] 
  -  3 [OK] 
  -  4 [空] 
  -  5 [OK ] 
  -  6 [空] 
  

需要看起来像：
C

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

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

代码我使用autoincrements所有值，不显示包含单词OK的单元格这样：

  C 
 
  -  1 [1] 
  -  2 [2 ] 
  -  3 [空] 
  -  4 [4] 
  -  5 [空] 
  -  6 [6] 
  pre> 
 
 
代码：
  Sub Macro1（）
 mynumber = 1 
 Dim r As Range，cell As Range 
 Set r = Range（Range（C1），Range（C2642）。End（xlD自己））
对于每个单元格在r 
如果cell.Value = OK然后cell.Value = mynumber 
 mynumber = mynumber + 1 
下一个
 End Sub 
  
 
 
解决方案
您的代码很接近，但您遇到的问题是您的单元格值正在检查 OK 认为它是一个变量。为避免这种情况，您首先要做的是将$ code> Option Explicit 放在每个模块的顶部，以便在没有声明变量时通知您。 
 
 
 
现在你需要做的只是让'OK'一个字符串，如OK。您还需要在如果语句之前递增您的号码，否则即使不想要，也会继续增加。
 
 
 
编辑：你也可以通过在这个代码中定义 Range 方法中的单元格来简单地做一下。它不像使用 End 那么灵活，但如果你有一个固定的范围会做的。
 
 
 
尝试这个代码：
  Option Explicit 
 
 Sub Macro1（）
 Dim r As Range，cell As范围，mynumber As Long 
 
设置r =范围（C1：C2642）
 
 mynumber = 1 
对于每个单元格在r 
如果单元格然后
 cell.Value = mynumber 
 mynumber = mynumber + 1 
 End If 
 Next 
 End Sub 
  
 
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

- 1 [empty]
- 2 [empty]
- 3 [OK]
- 4 [empty]
- 5 [OK]
- 6 [empty]
Need to look like:
       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:
   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

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

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

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

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


                        
这篇关于vba循环通过列，如果某个值然后增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！