Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
JMay,
Looks to me like the first argument in the function should be changed... from: CellColor As Range to: CellColor As Integer Also change... from: iColor = CellColor.Interior.ColorIndex to: iColor = CellColor The other solution would be to simply specify a range as the first argument when calling the function. Regards, Jim Cone San Francisco, CA "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
Thanks for pointing that out. This function came straight from a newly
acquired Excel VBA book (I'm reading through)..... "Jim Cone" wrote in message ... JMay, Looks to me like the first argument in the function should be changed... from: CellColor As Range to: CellColor As Integer Also change... from: iColor = CellColor.Interior.ColorIndex to: iColor = CellColor The other solution would be to simply specify a range as the first argument when calling the function. Regards, Jim Cone San Francisco, CA "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function debuging help
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |