Calculate the SUM from One Place to Another

[xbanditlordx]

I currently have a spreadsheet setup as:


Apple
100



Banana
50



Total Fruit





I want to know the total of the fruits (where there are more fruits in my actual spreadsheet).

Currently I have:



Sub Sum()

Dim FirstFruit As Range
Dim LastFruit As Range
Dim TotalFruits As Range

Set TotalFruits = Range("A:A").Find("Total Fruits", MatchCase:=True, Lookat:=xlPart).Offset(0, 1)


Set FirstFruit = Range("A:A").Find("Apple", MatchCase:=True, Lookat:=xlPart)
Set LastFruit = Range("A:A").Find("Banana", MatchCase:=True, Lookat:=xlPart)

TotalFruits = ?????????????????????????????

End Sub


How do I basically say that I want TotalFruits to be equal to the SUM of cells that exists between FirstFruit and LastFruit?

[SamT]

TotalFruits = WorksheetFunction.Sum(Range(FirstFruit.Offset(, 1), LastFruit.Offset(, 1))

However... set your Find for the first fruit starting at Cells(Rows.count, "A"), search Next
set your Find for the last fruit starting at Range("A1"), search Previous

[xbanditlordx]

Thank you for the help!

I struggled through my current code and without making any changes, I just had:

TotalFruits = Application.Sum(Range(FirstFruit.Offset(0, 1).Address, LastFruit.Offset(0, 1).Address))


which worked perfectly. Thanks again :)