Results 1 to 7 of 7

VBA to calculate cells or ranges

This is a discussion on VBA to calculate cells or ranges within the Excel Questions forums, part of the Question Forums category; Hi all, I want to calculate (sum) a range using VBA , its part of a bigger programme, however I ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    84

    Default VBA to calculate cells or ranges

    Hi all,

    I want to calculate (sum) a range using VBA, its part of a bigger programme, however I cannot get it working.

    Trying variety of ways including
    Code:
    'trying to add each cell value
    range (a1:a5).select
    selection.calculate.copy
    'want to paste the total to cell a6
    range(a6).paste

    This is the basic idea. However, I'm working between worksheets/workbooks. Do I need to declare a varaible to store? I tried this and
    Code:
    set myvarible = range(a1:a5).calcuate
    but did not work

    Went through a few RTMs and could not find a clear solution to the problem.

    Any guidence on this?

    Thanks Lawdie

  2. #2
    Board Regular
    Join Date
    Nov 2002
    Location
    Cornwall UK
    Posts
    589

    Default Re: VBA to calculate cells or ranges

    Code:
        Range("A6").Activate
        ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
    Try that in an aprropriate sub routine.

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Location
    Cornwall UK
    Posts
    589

    Default

    range ("a1:a5").select
    The range property is a string and therefore must be in double-quotes as shown.

    set myvarible = range("a1:a5") could be addressed as Range(myvariable)

  4. #4
    Board Regular
    Join Date
    Oct 2002
    Posts
    84

    Default Re: VBA to calculate cells or ranges

    Hi Whisperer14

    Thanks for the reply, I have the answer working as an msgbox output. But what am I doing wrong in the pasting line?

    Code:
    Sub going()
    
    Dim myrange As Range
    Dim answer As Integer
    
    Set myrange = Worksheets("Sheet1").Range("A2:a5")
    answer = Application.WorksheetFunction.Sum(myrange)
    
    'pasting the answer to a cell
    Range("a6").select
    ActiveCell.PasteSpecial (answer)
    
    End Sub
    Regards, Lawdie

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Posts
    84

    Default Re: VBA to calculate cells or ranges

    All

    I think I have the concept. Please look over and see if I'm clear on the example

    Code:
    Sub going()
    
    Dim myrange As Range
    Dim answer As Integer
    
    Set myrange = Worksheets("Sheet1").Range("A2:a5")
    answer = Application.WorksheetFunction.Sum(myrange)
    
    Range("a6").Select
    Range("a6").Value = answer
    
    
    End Sub
    If theres a shorter way around this or my code is verbose then let me know

    Thanks, Lawdie

  6. #6
    Board Regular
    Join Date
    Nov 2002
    Location
    Cornwall UK
    Posts
    589

    Default

    Sub Tryit()
    Range("A6").Value = Application.WorksheetFunction.Sum(Range("A1:A5"))
    End Sub

    is a slightly shorter way of achieving your result

  7. #7
    Board Regular
    Join Date
    Oct 2002
    Posts
    84

    Default Re: VBA to calculate cells or ranges

    Thanks Whisperer14

    Its much quicker and I have the understanding to go on

    Cheers Lawdie

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com