# Thread: Calculate the SUM from One Place to Another

1. ## Calculate the SUM from One Place to Another

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?

2. 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

3. Thank you for the help!

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