PDA

View Full Version : Solved: Calculation of an UDF



cactus
03-16-2011, 06:21 AM
Hi,

I have a very simple UDF that counts non merged cells in a specific range, but it dont recalculate if i change (merge) a new cell in the range...(calculation is set to automatic)
The only way to get the recalculation is editing the cell with the formula...

Any idea?

Function Ocupadas(celda As RANGE) As Integer
Application.Volatile
B = 0
For Each cel In celda.Cells



a = cel.MergeCells
If a = True Or cel.Value <> "" Then
B = B
Else
B = B + 1
End If
Next
Ocupadas = B


End Function



Thanks in advance

mikerickson
03-16-2011, 06:54 AM
That's true.
Un/merging cells does not trigger calculation, even of a volatile function.
Similarly, coloring a cell does not trigger calculation.

Which is why it is bad practice to use the color of a cell or its mergdness as data.
There are other reasons to avoid merged cells altogether.

cactus
03-16-2011, 07:11 AM
ups....too bad to hear that because I can't change the layout...it not depends on me
any workaround?

thanks again.-

Bob Phillips
03-16-2011, 08:20 AM
The only way I can think is to provide a button to merge and another to unmerge where you fore a recalculation.

cactus
03-16-2011, 11:01 AM
Ok.
Thaks for the help. I'll try with a button.