#VALUE Error from SUM Function in VBA

Question

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", _

                                                                "Employees"))

        GrpSht.Activate

        LstDtaRw = Range("A:A").Find(What:="*", _

                   SearchDirection:=xlPrevious, _

                   LookIn:=xlValues, _

                   SearchOrder:=xlColumns).Row

        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"), _

                        Key3:=Range("D5")

        ' 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

Tanks,

Will

Answer 1

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

Answer 2

I had the same problem and your explanation sorted it.

Thanks

Answer 3

Thanks for the feedback!

Answer 4

Ah, so it does!

I figured it had to be a simple ommission but I just wasn't seeing it.

Got it wrong thre first time & just kep on duplicating it.

Thank you.