none
Get math results of cells from another sheet RRS feed

  • Question

  • Hi All

    I am trying to retrieve the sum of 4 cells, divided by 60 from another sheet named QuoteData.

    I can retrieve single cells like this, but can't seem to "do the math".

    The following does not work

    Selection.Offset(0, 16).Value = Range("QuoteData!Q15:Q18").Value / 60

    Please advise what might work.

    TIA,

    Chuck, CABGx3


    Chuck, CABGx3

    Wednesday, July 13, 2016 7:34 PM

Answers

  • You can apply the worksheet function SUM to the range:

    Selection.Offset(0, 16).Value = Application.WorksheetFunction.Sum(Worksheets("QuoteData").Range("Q15:Q18")) / 60

    Note that we don't use .Value here - SUM applies directly to a range. And we specify the worksheet first, then the range.

    It is allowed to omit the WorksheetFunction object:

    Selection.Offset(0, 16).Value = Application.Sum(Worksheets("QuoteData").Range("Q15:Q18")) / 60

    Application.WorksheetFunction.Sum and Application.Sum have a slightly different effect if SUM results in an error, but that is not important here.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Wednesday, July 13, 2016 10:37 PM
    Wednesday, July 13, 2016 8:04 PM

All replies

  • You can apply the worksheet function SUM to the range:

    Selection.Offset(0, 16).Value = Application.WorksheetFunction.Sum(Worksheets("QuoteData").Range("Q15:Q18")) / 60

    Note that we don't use .Value here - SUM applies directly to a range. And we specify the worksheet first, then the range.

    It is allowed to omit the WorksheetFunction object:

    Selection.Offset(0, 16).Value = Application.Sum(Worksheets("QuoteData").Range("Q15:Q18")) / 60

    Application.WorksheetFunction.Sum and Application.Sum have a slightly different effect if SUM results in an error, but that is not important here.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Wednesday, July 13, 2016 10:37 PM
    Wednesday, July 13, 2016 8:04 PM
  • Your solution works great Hans...........

    Many, many thanks,

    Chuck, CABGx3


    Chuck, CABGx3

    Wednesday, July 13, 2016 10:39 PM