excel - 将 'If' 公式重复到最后一行

Question

我有一个工作表，其中有一个 VBA 中的 IF 公式，可以在 V9:Z9 中搜索类似值(DIST8 和 DIST6)。该公式用 1 或 0 填充 AO9。这个公式适合我的目的。我想重复这个公式直到最后一行。

Dim DIST8 As String
Dim DIST6 As String
DIST8 = "DIST8"
      ' Select cell A9, *first line of data*.
      Range("A9").Select
      ' Set Do loop to stop when an empty cell is reached.
      Do Until IsEmpty(ActiveCell)
         ' Insert your code here
                If Worksheets("Sheet1").Range("v9") Like "*" & "DIST8" & "*" Or Worksheets("Sheet1").Range("w9") Like "*" & "DIST8" & "*" Or Worksheets("Sheet1").Range("x9") Like "*" & "DIST8" & "*" Or Worksheets("Sheet1").Range("y9") Like "*" & "DIST8" & "*" Or Worksheets("Sheet1").Range("z9") Like "*" & "DIST8" & "*" Or Worksheets("Sheet1").Range("v9") Like "*" & "DIST6" & "*" Or Worksheets("Sheet1").Range("w9") Like "*" & "DIST6" & "*" Or Worksheets("Sheet1").Range("x9") Like "*" & "DIST6" & "*" Or Worksheets("Sheet1").Range("y9") Like "*" & "DIST6" & "*" Or Worksheets("Sheet1").Range("z9") Like "*" & "DIST6" & "*" Then
                    Worksheets("Sheet1").Range("Ao9").Value = 1
                Else
                    Worksheets("Sheet1").Range("Ao9").Value = 0
                End If
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop

不幸的是，循环无法正常工作，工作表将向下滚动到最后一行，但不会填充第一行之外的 IF。我想运行相同的 IF 公式但更改行号，例如； v9 到 v10、w9 到 w10 等

实现这一目标的最佳方法是什么？

Answer 1

要使用循环，您需要不断提高 Range("v9")、Range("Ao9") 等中的行号，否则您将只需比较新值并将新值写入相同的单元格即可。

    Dim DIST8 As String, DIST6 As String, r As Long, lr As Long

    DIST6 = "DIST6"  '<~~ don't forget DIST6 or the wildcards will make everything a 1.
    DIST8 = "DIST8"

    With Worksheets("Sheet1")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        For r = 9 To lr
            If .Range("v" & r) Like "*" & DIST8 & "*" Or .Range("w" & r) Like "*" & DIST8 & "*" Or _
               .Range("x" & r) Like "*" & DIST8 & "*" Or .Range("y" & r) Like "*" & DIST8 & "*" Or _
               .Range("z" & r) Like "*" & DIST8 & "*" Or .Range("v" & r) Like "*" & DIST6 & "*" Or _
               .Range("w" & r) Like "*" & DIST6 & "*" Or .Range("x" & r) Like "*" & DIST6 & "*" Or _
               .Range("y" & r) Like "*" & DIST6 & "*" Or .Range("z" & r) Like "*" & DIST6 & "*" Then
                .Range("Ao" & r).Value = 1
            Else
                .Range("Ao" & r).Value = 0
            End If
        Next r

    End With

您使用的循环适用于 25-500 条记录，但可以通过批量加载公式然后恢复为值来加快速度。如果您处理的记录超过 5K，速度的提升将会非常明显。

    Dim DIST8 As String, DIST6 As String, lr As Long

    DIST6 = "DIST6"  '<~~ don't forget DIST6 or the wildcards will make everything a 1.
    DIST8 = "DIST8"

    With Worksheets("Sheet1")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        With .Range(.Cells(9, "AO"), .Cells(lr, "AO"))
            .Formula = "=SIGN(SUM(COUNTIF(V9:Z9, {""*" & DIST6 & "*"", ""*" & DIST8 & "*""})))"
            .Value = .Value
        End With
    End With

请注意，后一个例程的 COUNTIF function不区分大小写，而原始模式匹配默认区分大小写。如果您希望对模式匹配进行不区分大小写的比较，请将Option Compare Text放在模块表顶部的声明部分。