Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Custom Sum Function
Greetings,
I have used the Function below to sum up cells in a column which contain red font numbers. I find that it works very well, however, it does not automatically update. I found that if I change the font of an existing cell entry to red and then enter a number in the same column and press return, the cell containing the SumRed function does update, however, none of the other columns update. I saw that Chip Pierson commented on a similar function on his wonderful site and said that if I used Alt+F9, the cell containing the function would update. This doesn't seem work at all. Is there a work around or am I relegated to writing a macro to enter a value in a cell in each column and then use the same macro to erase the cell entry? Thanks in advance for your help! Ray Function SumRed(SelectedCells As Range) ' Adds the values of the cells where the font color is red(3). Dim Cell As Object Dim x As Double x = 0 For Each Cell In SelectedCells If Cell.Font.ColorIndex = 3 Then x = x + Cell.Value End If Next Cell SumRed = x End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Custom Sum Function
Hi Ray,
Sadly changing the format of a cell doesn't tell XL that it needs to recalc :-( One solution is to declare the function as Volatile. To do this you need to enter the line: Application.Volatile at the start of the function. This means the function will be called by XL whenever it does a recalc of the spreadsheet. If you make this change your spreadsheet will take longer to calculate, but it should update your function. To trigger a recalc simply enter a random value in any old cell. HTH Peter Beach "Ray Batig" wrote in message ink.net... Greetings, I have used the Function below to sum up cells in a column which contain red font numbers. I find that it works very well, however, it does not automatically update. I found that if I change the font of an existing cell entry to red and then enter a number in the same column and press return, the cell containing the SumRed function does update, however, none of the other columns update. I saw that Chip Pierson commented on a similar function on his wonderful site and said that if I used Alt+F9, the cell containing the function would update. This doesn't seem work at all. Is there a work around or am I relegated to writing a macro to enter a value in a cell in each column and then use the same macro to erase the cell entry? Thanks in advance for your help! Ray Function SumRed(SelectedCells As Range) ' Adds the values of the cells where the font color is red(3). Dim Cell As Object Dim x As Double x = 0 For Each Cell In SelectedCells If Cell.Font.ColorIndex = 3 Then x = x + Cell.Value End If Next Cell SumRed = x End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Custom Sum Function
Hi Peter,
Thanks. I can't see a difference in the calc time since the machine is way faster than I am!! Ray Peter Beach wrote in message ... Hi Ray, Sadly changing the format of a cell doesn't tell XL that it needs to recalc :-( One solution is to declare the function as Volatile. To do this you need to enter the line: Application.Volatile at the start of the function. This means the function will be called by XL whenever it does a recalc of the spreadsheet. If you make this change your spreadsheet will take longer to calculate, but it should update your function. To trigger a recalc simply enter a random value in any old cell. HTH Peter Beach "Ray Batig" wrote in message ink.net... Greetings, I have used the Function below to sum up cells in a column which contain red font numbers. I find that it works very well, however, it does not automatically update. I found that if I change the font of an existing cell entry to red and then enter a number in the same column and press return, the cell containing the SumRed function does update, however, none of the other columns update. I saw that Chip Pierson commented on a similar function on his wonderful site and said that if I used Alt+F9, the cell containing the function would update. This doesn't seem work at all. Is there a work around or am I relegated to writing a macro to enter a value in a cell in each column and then use the same macro to erase the cell entry? Thanks in advance for your help! Ray Function SumRed(SelectedCells As Range) ' Adds the values of the cells where the font color is red(3). Dim Cell As Object Dim x As Double x = 0 For Each Cell In SelectedCells If Cell.Font.ColorIndex = 3 Then x = x + Cell.Value End If Next Cell SumRed = x End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Format Question | Excel Discussion (Misc queries) | |||
Custom List Question | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
custom list question | Excel Discussion (Misc queries) | |||
Adding a custom function to the default excel function list | Excel Programming |