VBA to calculate cells or ranges

lawdie

Board Regular
Joined
Oct 28, 2002
Messages
84
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Code:
    Range("A6").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
Try that in an aprropriate sub routine.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Sub Tryit()
Range("A6").Value = Application.WorksheetFunction.Sum(Range("A1:A5"))
End Sub

is a slightly shorter way of achieving your result
 
Upvote 0
Thanks Whisperer14

Its much quicker and I have the understanding to go on

Cheers Lawdie
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top