#name!

[hanif]

Hi all,

I have the below code to sum a range of valued in the last colum but the return value is #NAME! does anyone know where am I going wrong?

    ESlc = Sheets("Energy Summary").Cells(10, Columns.Count).End(xlToLeft).Column
    
    With Sheets("Energy Summary")
        TotalLC = .Cells(22, Columns.Count).End(xlToLeft).Column
        str2 = .Range(.Cells(10, ESlc), .Cells(21, ESlc)).Address
        
        .Cells(22, TotalLC + 1) = Application.Evaluate("SUM(Energy Summary!str2)")
    End With

Thanks

[hanif]

I also tried the below code and I am getting an erorr message saying "Unable to get the Sum poperty of the worksheetfunction class.

.Cells(22, TotalLC + 1) = Application.WorksheetFunction.Sum(str2)

[bakerman2]

Try this.

Sub tst()
   With Sheets("Energy Summary")
        ESlc = .Cells(10, Columns.Count).End(xlToLeft).Column
        TotalLC = .Cells(22, Columns.Count).End(xlToLeft).Column
        .Cells(22, TotalLC + 1) = Application.Sum(.Range(.Range(.Cells(10, ESlc), .Cells(21, ESlc)).Address))
    End With
End Sub

[hanif]

Hi Bakerman,

Thanks for the reply. It still doe not work. after running the code the cell shows blank but when I click on the cell shown 0 in the formula bar

[hanif]

Thanks bakerman I was able to fix it but your reply helped a lot to get that fixed.

[bakerman2]

Just to show you that it does work.

[Norie]

Perhaps.

    ESlc = Sheets("Energy Summary").Cells(10, Columns.Count).End(xlToLeft).Column
    
    With Sheets("Energy Summary")
        TotalLC = .Cells(22, Columns.Count).End(xlToLeft).Column
        str2 = .Range(.Cells(10, ESlc), .Cells(21, ESlc)).Address
        
        .Cells(22, TotalLC + 1) = Application.Evaluate("SUM('Energy Summary'!" & str2 & ")")
    End With

[bakerman2]

Glad you figured it out. Thanks for the rep points.