Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Sum colored cells values not working
Hi all, I have tried formula "=SumColor(A1,B2:B10,C2:C10)" in cell D1
after putting VBA function in module (see below) but I am getting error "#VALUE". Basically I am tring to Sum cells values of a column which are in the same rows of coloured cells of another column. I have tow question. One how can i solve my fuction code and Two that when you make your own function in excel VBA, with what code in your function you can display Syntax while that function is applied. For example when you put "IF" formula in the cell and as soon as you put "IF(" you will see small box appearing with displaying Syntax like "IF(logical_test,value_if_true,value_if_false) ". I want to do exactly same in my function that when i put "=SumColor(" in the cell then little box should appear displaying "SumColor(color_cell,color_range,sum_range)". Please can any friend can help me on this. Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rcolRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(Columns(rCell.Row, rSumRange.Column)) + vResult End If Next rCell SumColor = vResult End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Sum colored cells values not working
On 9 July, 06:27, K wrote:
Hi all, *I have tried formula "=SumColor(A1,B2:B10,C2:C10)" in cell D1 after putting VBA function in module (see below) but I am getting error "#VALUE". *Basically I am tring to Sum cells values of a column which are in the same rows of coloured cells of another column. *I have tow question. *One how can i solve my fuction code and Two that when you make your own function in excel VBA, with what code in your function you can display Syntax while that function is applied. *For example when you put "IF" formula in the cell and as soon as you put "IF(" you will see small box appearing with displaying Syntax like "IF(logical_test,value_if_true,value_if_false) ". *I want to do exactly same in my function that when i put "=SumColor(" in the cell then little box should appear displaying "SumColor(color_cell,color_range,sum_range)". Please can any friend can help me on this. Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rcolRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(Columns(rCell.Row, rSumRange.Column)) + vResult End If Next rCell SumColor = vResult End Function hi question 1. see this site http://cpearson.com/excel/colors.aspx this is the functions i use and they work. Quetion 2. i don't think UDFs work that way. the display syntax only works with build in xl functions. or at least they never work for my UDF's, regards FSt1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Sum colored cells values not working
Try this variation of your function
Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim tmp As Variant Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rcolRange If rCell.Interior.ColorIndex = iCol Then tmp = rSumRange.Cells(rCell.Row - rcolRange.Row + 1, 1) If IsNumeric(tmp) Then vResult = vResult + tmp End If Next rCell SumColor = vResult End Function -- HTH Bob "K" wrote in message ... Hi all, I have tried formula "=SumColor(A1,B2:B10,C2:C10)" in cell D1 after putting VBA function in module (see below) but I am getting error "#VALUE". Basically I am tring to Sum cells values of a column which are in the same rows of coloured cells of another column. I have tow question. One how can i solve my fuction code and Two that when you make your own function in excel VBA, with what code in your function you can display Syntax while that function is applied. For example when you put "IF" formula in the cell and as soon as you put "IF(" you will see small box appearing with displaying Syntax like "IF(logical_test,value_if_true,value_if_false) ". I want to do exactly same in my function that when i put "=SumColor(" in the cell then little box should appear displaying "SumColor(color_cell,color_range,sum_range)". Please can any friend can help me on this. Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rcolRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(Columns(rCell.Row, rSumRange.Column)) + vResult End If Next rCell SumColor = vResult End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Sum colored cells values not working
On 09/07/2010 11:27, K wrote:
Hi all, I have tried formula "=SumColor(A1,B2:B10,C2:C10)" in cell D1 after putting VBA function in module (see below) but I am getting error "#VALUE". Basically I am tring to Sum cells values of a column which are in the same rows of coloured cells of another column. I have tow question. One how can i solve my fuction code and Two that when you make your own function in excel VBA, with what code in your function you can display Syntax while that function is applied. For example when you put "IF" formula in the cell and as soon as you put "IF(" you will see small box appearing with displaying Syntax like "IF(logical_test,value_if_true,value_if_false) ". I want to do exactly same in my function that when i put "=SumColor(" in the cell then little box should appear displaying "SumColor(color_cell,color_range,sum_range)". Please can any friend can help me on this. Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range) Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rcolRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(Columns(rCell.Row, rSumRange.Column)) + vResult End If Next rCell SumColor = vResult End Function Hi K, This isn't straightforward (at least in the versions of Excel I have used - upto & including 2003) Look at http://www.eng-tips.com/viewthread.cfm?qid=79365&page=9 for some helpful discussion & links Rgds. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Sum colored cells values not working
Or if your excel is bang up to date:
One of the new features in Excel 2010 is the ability to provide argument descriptions for user-defined functions. These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box. http://spreadsheetpage.com/index.php...n_excel_20 10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find function not working with merged cells | Excel Programming | |||
Delete values colored cells | Excel Programming | |||
Returning a colored value in If function | Excel Discussion (Misc queries) | |||
"Sum only the colored cells" custom function | Excel Programming | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |