UDF help

[Sylvan]

Kind of tricky to explain. I've written a UDF to sum bolded values until the next cell with a fill color is encountered. It works fine until it's copied down as part of a range and then I get a #VALUE! error. I'll then enter some values and it starts working. But then when I run a macro to paste a range below the UDF it gives me the #VALUE! error again.

Here's the code -- any ideas? Thanks in advance

Function ROOMSUM(MyRange As Range) As Double
'Sums bolded values down until the next colored cell

Dim rCell As Range

For Each rCell In MyRange
If rCell.Interior.ColorIndex <> xlNone Then Exit Function
If rCell.Font.Bold = True Then
ROOMSUM = ROOMSUM + rCell.Value
End If
Next rCell

End Function

 

[Sylvan]

Seems to be the first empty cell below the function that's causing the problem. There's nothing in the cell, but when I delete it, the error goes away. Also, the entire column has the same currency formatting.

 

[Michael M]

This will BUMP you back to the top.....but,
what value is ROOMSUM to start with....and it isn't dimmed either.

 

[Peter_SSs]

This will BUMP you back to the top.....but,
what value is ROOMSUM to start with....and it isn't dimmed either.

ROOMSUM is the function name, it is declared (as Double) so it's initial value will be zero.

 

[Michael M]

hmmm, I'm afraid I've had a seniors moment Peter.....I got the BUMP right though !!!

 

[mikerickson]

Try this, it avoids the TypeMismatch if the cell holds non-numeric data.

Function ROOMSUM(MyRange As Range) As Double
'Sums bolded values down until the next colored cell

Dim rCell As Range

For Each rCell In MyRange
    If rCell.Interior.ColorIndex <> xlNone Then Exit Function
    If rCell.Font.Bold = True Then
        ROOMSUM = ROOMSUM + Val(CStr(rCell.Value))
    End If
Next rCell

End Function

 

[Sylvan]

No, that didn't do it. Error checking is telling me that a value used in the formula is of the wrong data type, but the whole column is numbers.

 

[Peter_SSs]

It works fine until it's copied down as part of a range and then I get a #VALUE! error. I'll then enter some values and it starts working. But then when I run a macro to paste a range below the UDF it gives me the #VALUE! error again.

Tell us a bit more about this. Some things that may possibly throw some light on the problem:

1. Where is the data you are summing (eg B2:B10)?

2. Where did the data in this range come from? (Manually typed, vba code, copied from a webpage, etc)

3. What cell is your UDF formula in?

4. What, exactly, is the UDF formula in that cell?

5. What cell/range are you Copying? (This and the next 2 questions are referring to the red text above, not the copying of more data in to the data column)

6. Where are you Pasting what you have copied.

7. Is the Copy/Paste manual or macro?

 

[Sylvan]

Peter,

1. UDF is in cell N12 and working fine summing data in cells N14:N953.

2. Some of the data in this range was typed, but most of it is the sums of data from each row.

3. See 1.

4. As it stands right now the UDF is:

Function ROOMSUM(MyRange As Range) As Currency
'Sums bolded values between colored cells

Dim rCell As Range

For Each rCell In MyRange
If rCell.Interior.ColorIndex <> xlNone Then Exit Function
If rCell.Font.Bold = True Then
ROOMSUM = ROOMSUM + CDbl(rCell.Value)
End If
Next rCell

End Function

5. The problem starts with the copying. When I use a macro to copy the range a12:q15 (which obviously includes the UDF), the new version of the formula (now in N19 and summing data in n21:n960) gives me the error. If I clear cell N20, the error goes away. If I use the macro to copy and paste the range again (will copy the same range leaving one empty row below the last used cell) every version of the UDF will give me an error (except the original which never had a problem). If I clear any empty cell in the N column, every version of the UDF above the now cleared cell will resolve. All the cells in the column are formatted as currency, so I can't figure out why it would tell me something is the wrong data type. If I get all the UDFs working and then copy and paste my original range again, they all give me an error again.

6. and 7. See 5.

Hope that clarifies things. I appreciate any help.

 

[shg]

There's no reason to declaring the function as Currency; the only data type for numeric cells is Double.

Here's a debug version of the function you might try. Look in the Immediate window when the code stops to see the offending cell.

Function ROOMSUM(MyRange As Range) As Double
    Dim rCell     As Range
 
    On Error GoTo Oops
    For Each rCell In MyRange
        With rCell
            If .Interior.ColorIndex <> xlColorIndexNone Then Exit Function
            If .Font.Bold = True Then
                If VarType(.Value2) = vbDouble Then
                    ROOMSUM = ROOMSUM + .Value2
                Else
                    GoTo Oops
                End If
            End If
        End With
    Next rCell
    Exit Function
 
Oops:
    Debug.Print Application.ThisCell.Address, rCell.Address
    Stop
End Function