#VALUE Error from SUM Function in VBA

In Excel 2007 (Windows XP), I have 3 worksheets which contain SUM functions in each cell (from row 5 down) in columns F - I & K - N.

These formulas refer to source ranges on other sheets which are populated by queries.

When the queries run, these cells display the correct values from the queries.

The formula is "=SUM(IF(ISNA(VLOOKUP($A457,query result table 1,5,FALSE)),0,VLOOKUP($A457,query result table 1,5,FALSE)),IF(ISNA(VLOOKUP($A457,query result table 2,5,FALSE)),0,VLOOKUP($A457,query result table 2,5,FALSE)))".


I am trying, in VBA, to add a total footer at the bottom of each of the 3 result sheets.

Since the number of rows returned by the queries is variable, I determine on which row (TotRw) to position the footer, and my macro uses the SUM function in each applicable column to sum all the rows above that, from row 5 down.


In each of these footer cells, I get #VALUE (hovering in debug shows 'Error 2015')

If I manually enter the SUM function on the worksheet, summing from row 5 down, I get the numeric value I expect.


What am I missing in my VBA code?



    For Each GrpSht In Sheets(Array("Officers", _
                                                                "Non_Employees", _
        LstDtaRw = Range("A:A").Find(What:="*", _
                   SearchDirection:=xlPrevious, _
                   LookIn:=xlValues, _
        If LstDtaRw > 4 Then
        ' Sort the group by Last Name, First Name, Middle Initial.
            GrpSht.Unprotect (Psw)
            Range("A5:D" & LstDtaRw).Select
            Selection.Sort _
                        Key1:=Range("B5"), _
                        Key2:=Range("C5"), _
        ' Add total footer to list.
            TotRw = LstDtaRw + 1
            Range("C" & TotRw).Value = "Totals"
            Range("C" & TotRw).HorizontalAlignment = xlRight
            Range("D" & TotRw).Value = "-"
            Range("D" & TotRw).HorizontalAlignment = xlCenter
            Range("E" & TotRw).Value = Year(Date)
            Range("F" & TotRw).Value = Application.Sum("F5:F" & LstDtaRw)
            Range("G" & TotRw).Value = Application.Sum("G5:G" & LstDtaRw)
            Range("H" & TotRw).Value = Application.Sum("H5:H" & LstDtaRw
            Range("I" & TotRw).Value = Application.Sum("I5:I" & LstDtaRw)
            Range("J" & TotRw).Value = Year(Date) - 1
            Range("K" & TotRw).Value = Application.Sum("K5K" & LstDtaRw)
            Range("L" & TotRw).Value = Application.Sum("L5:L" & LstDtaRw)
            Range("M" & TotRw).Value = Application.Sum("M5:M" & LstDtaRw)
            Range("N" & TotRw).Value = Application.Sum("N5:N" & LstDtaRw)
            With Range("F" & TotRw & ":I" & TotRw, _
                       "K" & TotRw & ":N" & TotRw).Borders(xlEdgeTop)
                .LineStyle = xlDouble
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
            End With
            GrpSht.Protect Psw)
        End If
    Next GrpSht





Question Info

Last updated February 22, 2018 Views 1,789 Applies to:

In VBA Sum wants a Range rather than a String, so:

y = Application.WorksheetFunction.Sum(Range("A1:A10"))

will work
x = Application.WorksheetFunction.Sum("A1:A10")

won't work


1 person was helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.