Last Cell Computation ok in Sub but Not in Function

mhabib · Mar 15th 2006

Hi all,

I've written a UDF to sum up all the bold numbers in a 1-column range. The initial code worked fine, except that if the specified range was an entire column then the computation took some time to process all 65536 rows. I added the condition that only those cells should be processed whose row is less than or equal to the row of the last used cell. For this I added the variable LastRow. Although the formula for computing LastRow works correctly in a Sub procedure, the value returned within the Function, for reasons best known to Mr. Gates, is always 1.

Would appreciate any help with this. My code is reproduced below.

Thanks

m

Code

Function SUMBOLD(rngSumRange As Range) As Variant
    'LastRow returns erroneous result in function.


    Dim rCell As Range
    Dim LastRow As Long
    
    LastRow = rngSumRange.SpecialCells(xlCellTypeLastCell).Row


    SUMBOLD = 0


    If rngSumRange.Columns.Count > 1 Then SUMBOLD = "NUM!": Exit Function


    For Each rCell In rngSumRange
        If rCell.Row > LastRow Then Exit Function
        If IsNumeric(rCell) And rCell.Font.Bold = True Then
            SUMBOLD = SUMBOLD + rCell
        End If
    Next


End Function

Display More

Code

Sub Junk()
    'LastRow computs fine in this.




    Dim rngSumRange As Range
    Dim LastRow As Long
    
    Set rngSumRange = Application.InputBox("Select Range", , , , , , , 8)
    MsgBox rngSumRange.Worksheet.Name
    
    LastRow = rngSumRange.SpecialCells(xlCellTypeLastCell).Row
    MsgBox LastRow
End Sub

Display More

**Andy Pope** · Mar 15th 2006

Re: Last Cell Computation ok in Sub but Not in Function

Hi,

Interesting the same syntax when called from a worksheet cell gives a different result. In the cell =Test() returns $A:$A while the subroutine returns, in my example, $A$25 which is the last cell.[vba]Function Test() As String

Test = Range("A:A").SpecialCells(xlCellTypeLastCell).Address

End Function

Sub X()

MsgBox Test

End Sub[/vba]

This mod should fix your UDF[vba]Function SUMBOLD(rngSumRange As Range) As Variant
'LastRow returns erroneous result in function.

Dim rCell As Range
Dim LastRow As Long

If rngSumRange.Columns.Count > 1 Then SUMBOLD = "NUM!": Exit Function

LastRow = rngSumRange.Cells(rngSumRange.Cells.Count, 1).End(xlUp).Row
SUMBOLD = 0
For Each rCell In rngSumRange
If rCell.Row > LastRow Then Exit Function
If IsNumeric(rCell) And rCell.Font.Bold = True Then
SUMBOLD = SUMBOLD + rCell
End If
Next

End Function[/vba]

mhabib · Mar 15th 2006

Re: Last Cell Computation ok in Sub but Not in Function

Thanks Andy. Works great!

m

Last Cell Computation ok in Sub but Not in Function

Participate now!

Share