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

 

Question Info


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

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

GSNU201507

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.