Function debuging help
Hi Jim from Va,
You're right, that's quite an editorial gaffe. That SUM call is so
unnecessary as well, so all-in, a bit slap-dash, I hope it's not one that I
recommend<g.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Jim May" wrote in message
news:Rr%Ac.3061$HN5.1905@lakeread06...
Bob:
Thanks for your usual kind assistance; What I posted (the function) came
straight from my most recent book purchase - the title and author I will
leave unnamed (to protect unnecessary flaming,,,) another page which
bypassed the edit room (LOL)...
I changed my user name per your request (I'm Jim May - Virginia USA).
Again thanks,
Jim May
"Bob Phillips" wrote in message
...
JMay,
You define the first of the function arguments as a range, but pass a
number
to the function. You can either
pass the range with the colour
=SumByColor(A1,B4:B15)
maintain the call as is and change the code to handle an index
Function SumByColor(CellColor As Long, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
iColor = CellColor
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function
or allow either a range or an index
Function SumByColor(CellColor, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
If TypeName(CellColor) = "Range" Then
iColor = CellColor.Interior.ColorIndex
Else
iColor = CellColor
End If
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function
By the way, as you are iterating through each cell, you don't need to
sum
that range, that is
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
can be written more simply as
myTotal = mytestcell.Value + myTotal
Ande finally, as we see you so often, any chance of a more persoanl
handle
that we can address you by?
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"JMay" wrote in message
news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????
Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function
|