vba loop through column and if certain value then

问题:

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

回答1:

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

回答2:

You can do this without a macro:

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

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

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$1 with the first row your data start (or the header row) - and $A2 with 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