我对Excel的VBA很陌生,我已经在网上搜索了这个特定问题。也许是因为我是编码新手,答案对我来说并不突出,但它就在这里。
最初,我想使用一个公式来计算范围内的样式。在此之后,我想返回包含相同样式范围内的特定文本的单元格数。
例如,列表的样式为"好"、"坏"或"正常"。此外,它还会有鲍勃、乔或简。在另一个地方,我会尝试计算单词"Bob"出现样式"Good"的次数,并将其作为值返回。如果"Bob"总共出现 10 次,但其中只有 4 次样式良好,则计数将以"4"返回。此外,如果样式"好"出现 5 次,但其中只有 4 次包含文本"Bob",则计数将以"4"返回。
我有两个公式,但它们只返回特定文本的计数或特定样式的计数。另外,我想指定仅在公式中而不是在VBA模块中计数的文本。
VAR = CountStyleAndText("Bob",B2:B26(通过单元格 B2 和 B26 对"Bob"进行计数,样式为"良好">
这是我遇到的第一个代码,它计算一系列单元格中的样式
Function CountStyleGood(CellRange)
Dim Item As Range, Total As Long
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" Then
Total = Total + 1
End If
Next Item
CountStyleGood = Total
End Function
这是我遇到的第二个代码,它计算单元格范围内的文本(经过测试,但未能使其工作(
=计数("查找我",范围("A1:A100"((
function count(find as string, lookin as range) As Long
dim cell As Range
for each cell in lookin
if (cell.Value = find) then
count = count + 1 '//case sens
next
end function
我想这样的事情可以做到:
Function CountStyleAndText(CellRange, find As String, lookin As Range) As Long
Dim Item As Range, Total As Long, Cell As Range
For Each Item In CellRange
' Check to see if the cell is formatted as Style = "Good"
If Item.Style = "Good" And (cell.Value = find) Then
Total = Total + 1
End If
Next Item
CountStyleAndText = Total
End Function
请记住,这是您在单元格中键入的内容:
=CountStyleAndText("Bob",B1:B26(
如果要在"公式"中指定样式名称(即在UDF调用中(,请使用:
Function CountStyleGood(find As String, lookin As Range, styleName As String)
Dim Item As Range, Total As Long
For Each Item In lookin
If Item.Style = styleName Then If Item.Value = find Then Total = Total + 1
Next Item
CountStyleGood = Total
End Function
而如果要对样式名称进行硬编码:
Function CountStyleGood(find As String, lookin As Range)
Dim Item As Range, Total As Long
For Each Item In lookin
If Item.Style = "Good" Then If Item.Value = find Then Total = Total + 1
Next Item
CountStyleGood = Total
End Function
无论哪种情况,请记住样式名称区分大小写(即"好"与"好"不同(