英文:
Sum cells by colors based on other colum cells
问题
我想要对具有相同颜色的单元格求和。我知道有一些VBA函数可以做到这一点。但我的问题有点特殊。我想要根据另一列上的单元格颜色来对单列的单元格值求和。
我添加了一个示例和我使用的代码。在我尝试访问“Interior”属性的那一行上，我得到了“#VALUE”错误。
功能 SumByColor(CellColor 作为 Range, rRange 作为 Range)
Dim cSum 作为 Double
Dim ColIndex 作为 Integer
Dim compatedCell 作为 Range
Debug.Print ("sumbycolor called")
ColIndex = CellColor.Interior.ColorIndex
对于 Each cl 在 rRange
comparedCell = Worksheets("HA").Cells(cl.Row, 1)
Debug.Print (comparedCell.Interior.ColorIndex) #未打印任何内容
如果 comparedCell.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
下一个 cl
SumByColor = cSum
End Function
感谢您的帮助。
英文:
I wanna sum cells that have the same color. I know there are some VBA functions to do that. But my problem is kinda specific. I want to sum cells values from a single column, based on cells colors on another column.
I add an example and the code I used. I got the "#VALUE" error on the line where I try to access the Interior property.
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Double
Dim ColIndex As Integer
Dim compatedCell As Range
Debug.Print ("sumbycolor called")
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
comparedCell = Worksheets("HA").Cells(cl.Row, 1)
Debug.Print (comparedCell.Interior.ColorIndex) #nothing printed
If comparedCell.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
Thx for your help.
答案1
得分: 1
你应该将所有的变量都用 Dim 声明。
Dim cl As Range, comparedCell As Range
For Each cl In rRange
Set comparedCell = Worksheets("HA").Cells(cl.Row, 1)
Debug.Print (comparedCell.Interior.ColorIndex) '什么也不会打印出来
如果比较的单元格的 Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
由于 comparedCell 是一个 Range 对象，你必须使用 Set。
英文:
You should dim all your variables.
Dim cl As Range, comparedCell As Range
For Each cl In rRange
Set comparedCell = Worksheets("HA").Cells(cl.Row, 1)
Debug.Print (comparedCell.Interior.ColorIndex) 'nothing printed
If comparedCell.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
As comparedCell is a Range-object you have to use Set.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论