我是VBA的新手,目前正在尝试在用户定义的函数上应用循环。 定义的函数如下所示。
Function CountColor(InRange As range, ColorIndex As Long, _
Optional OfText As Boolean = False) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CountColor
' This function counts the cells in InRange whose ColorIndex
' is equal to the ColorIndex parameter. The ColorIndex of the
' Font is tested if OfText is True, or the Interior property
' if OfText is omitted or False. If ColorIndex is not a valid
' ColorIndex (1 -> 56, xlColorIndexNone, xlColorIndexAutomatic)
' 0 is returned. If ColorIndex is 0, then xlColorIndexNone is
' used if OfText is Fasle or xlColorIndexAutomatic if OfText
' is True. This allows the caller to use a value of 0 to indicate
' no color for either the Interior or the Font.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As range
Dim N As Long
Dim CI As Long
If ColorIndex = 0 Then
If OfText = False Then
CI = xlColorIndexNone
Else
CI = xlColorIndexAutomatic
End If
Else
CI = ColorIndex
End If
Application.Volatile True
Select Case ColorIndex
Case 0, xlColorIndexNone, xlColorIndexAutomatic
' OK
Case Else
If IsValidColorIndex(ColorIndex) = False Then
CountColor = 0
Exit Function
End If
End Select
For Each R In InRange.Cells
If OfText = True Then
If R.Font.ColorIndex = CI Then
N = N + 1
End If
Else
If R.Interior.ColorIndex = CI Then
N = N + 1
End If
End If
Next R
CountColor = N
End Function
我试图在子程序中使用 CountColor 函数,但它抛出了运行时 424 错误。
Sub Summary()
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
TOTALFAILS = CountColor((range(Cells(i, 4), Cells(i, LastColumn))), 38)
Cells(i, LastColumn + 8) = TOTALFAILS
Next i
End Sub
你能帮我找出我做错了什么吗?非常感谢你的帮助。 谢谢。
Range(Cells(i,4),Cells(i,LastColumn))
是一个范围参数,而不是两个。 - Vityata