时间戳

首页 > 解决方案 > 当 Excel VBA 中任一单元格值文本为“未执行”时，将文本插入 Excel 单元格

excel - 当 Excel VBA 中任一单元格值文本为“未执行”时，将文本插入 Excel 单元格

问题描述

当所有范围都通过时，以下代码可以正常工作。但我想要任何一个范围示例：G5 is "Not Executed"然后我想插入为SQL Error。

   Sub Test()

     If Worksheets("Before Conversion German Count").Range("G4:G80").Text = "PASS" Then
            Worksheets("INDEX").Range("F30") = "Completed"
         Worksheets("INDEX").Range("F30").Interior.ColorIndex = 43
 ElseIf Worksheets("Before Conversion German Count").Range("G4:G80").Text = "Not Executed" Then
          Worksheets("INDEX").Range("F30") = "SQL Error"
         Worksheets("INDEX").Range("F30").Interior.ColorIndex = 44
  Else: Worksheets("INDEX").Range("F30") = "Validation failed"
       Worksheets("INDEX").Range("F30").Interior.ColorIndex = 3
  End If

  End Sub

谁能帮我？

标签： excelvba

解决方案

正如我在评论中建议的那样，让我们​​使用 COUNTIF

Sub Result()
Const PASS = "PASS"
Const NOT_EXECUTED = "Not Executed"
Dim rg As Range

    Set rg = Worksheets("Before Conversion German Count").Range("G4:G80")

    With Worksheets("INDEX").Range("F30")
        If WorksheetFunction.CountIf(rg, PASS) = rg.Rows.Count Then
            .Value = "Completed"
            .Interior.ColorIndex = 43
        ElseIf WorksheetFunction.CountIf(rg, NOT_EXECUTED) = rg.Rows.Count Then
            .Value = "SQL Error"
            .Interior.ColorIndex = 44
        Else
            .Value = "Validation failed"
            .Interior.ColorIndex = 3
        End If
    End With

End Sub

推荐阅读