excel - VBA Countif 大写

Question

我正在尝试计算包含用户定义范围内所有大写字符的单元格实例的数量，我已经有一些代码可以循环并正确突出显示这些大写单元格，但我正在努力应用该逻辑到VBA 的 Countif 函数。这是我得到的代码，但它给出了不匹配错误:

'count instances of all caps
Dim allcaps As Long
allcaps = Application.CountIf(Range(rngCompany.Cells(1, 1), rngCompany.Cells(Lastrow, 1)), UCase(Range(rngCompany.Cells(1, 1), rngCompany.Cells(Lastrow, 1))))


MsgBox "There are " & allcaps & " uppercase company names to review."

正确突出显示单元格的代码是:

'Highlight all caps company names for review
With ws
    For i = 2 To Lastrow
        ' checks if cells in company name col are uppercase
        If rngCompany.EntireColumn.Cells(i, 1).Value = UCase(rngCompany.EntireColumn.Cells(i, 1).Value) Then
            wbk1.Sheets(1).Rows(i).Interior.ColorIndex = 6 '6: Yellow
        Else
            End If
        
    Next i
End With

有没有办法让 countif 代码在循环中以类似的方式工作？谢谢。

Answer 1

您可以这样做:

Function AllCapsCount(Target As Range) As Long
    With Target.Parent
        AllCapsCount = .Evaluate("=SUMPRODUCT(--EXACT(" & Target.Address & ",UPPER(" & Target.Address & ")))")
    End With
End Function