Results 1 to 3 of 3

Thread: Calculate the SUM from One Place to Another

  1. #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. #2
    Moderator VBAX Sage SamT's Avatar
    Oct 2006
    Near Columbia
    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
    I expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts