Last Cell Computation ok in Sub but Not in Function

  • 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.



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


    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
    End If

    End Function[/vba]


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!