Solved: Sub v Function

[mdmackillop]

http://vbaexpress.com/forum/showthread.php?t=16377
Re this question, I thought a simple UDF would suffice. I'm getting a "wrong" result with a Function, but correct answer with a Sub. Any ideas?


Sub CountJ1()
Dim r As Range
Set r = Range("J:J").SpecialCells(xlCellTypeConstants, 1)(1)
Selection = Application.Sum(Range(r, r.Offset(Range("U17")-1)))
End Sub

Function CountJ()
Dim r As Range
Set r = Range("J:J").SpecialCells(xlCellTypeConstants, 1)(1)
CountJ = Application.Sum(Range(r, r.Offset(Range("U17")-1)))
End Function

[RichardSchollar]

Hi Malcolm

The function also works if called from a sub. If called from the worksheet, r is being set to J1 for some reason - hence the return value of 5. Maybe something to do with SpecialCells not limiting the range being returned in col J when called from a worksheet cell???

Richard

[Bob Phillips]

It's SpecialCells Malcolm. Doesn't work in UDFs.

[mdmackillop]

Thanks Bob. I never knew that.

[Bob Phillips]

There are a few properties/methods that don't. SpecialCells sticks out, but I don't recall the others off of the top.

[mdmackillop]

Thanks both
I went with

Function CountJ()
Dim r As Range
For Each r In Range("J:J")
If Not (r.Formula Like "=*" Or r.Formula = "") Then
Exit For
End If
Next

CountJ = Application.Sum(Range(r, r.Offset(Range("U17") - 1)))
End Function