Solved

Adding a range in Excel

Posted on 2014-03-12
5
143 Views
Last Modified: 2014-03-16
What is the recommended way in adding a range in MS Excel VBA...When I search the Internet some suggest the below ?  Also if I wanted to sum a range, but I have the column number not the latter are there any suggestions for using the column number ?

Range("A1").Formula ="=Sum(A2:B3)"
0
Comment
Question by:upobDaPlaya
  • 2
  • 2
5 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39925537
How is your range defined? Can you describe the size of the range?

Kevin
0
 

Author Comment

by:upobDaPlaya
ID: 39925573
I have the below information

Start row    End Row
10               50
60               70
250             500

For each of the start and end points I want to sum columns A-Z.  Thus, I want to sum

A10:A50  in A51
A60:A70  in A71
A250:A500 in A501

b10:b50  in b51
b60:A70  in b71
b250:b500 in b501

etc..

My problem is I do not want to write out a line of code for each column start and end points.  I was hoping I could loop thru the column numbers to do the calculation as I know I always need to add columns A to Z (can you do a loop on letters a-z ?)  Note the start and end points are already stored in a worksheet which I dynamically create each time I run the code.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 39925577
You can add the formulas to the bottom of a section with one statement:

    ThisWorkbook.Worksheets("Sheet1").Rows(SummaryRow).Resize(1, 26).Formula = "=SUM(A" & FirstSumRow & ":A" & LastSummaryRow & ")"

All you have to do is provide SummaryRow, FirstSumRow, and LastSumRow. For the first section they will be:

SummaryRow = 51
FirstSumRow = 10
LastSumRow = 50

Kevin
0
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 250 total points
ID: 39927700
You could address the range with the Range-cell method Range(Cells(a,b),Cells(c,d)) where a,b,c,d are numerical values for rows and columns.

And the R1C1 method for the formula .FormulaR1C1 = "=SUM(R[-41]C:R[-1]C)" to make the sum for row 10 to 50, with the sum in row 51.
The notation R[-41]C means 41 rows above, same column, and R[-1]C means 1 row above, same column.

Then all references will be numerical.

The formula can be made using numerical variables.
For the first range from row 10 to 50.
FirstSumRow=10, LastSumRow=50, FirstColumn=1 (=A), LastColumn=26 (=Z).

ThisWorkbook.Worksheets("Sheet1").Range(Cells(LastSumRow + 1, FirstColumn), Cells(LastSumRow + 1, LastColumn)).FormulaR1C1 = "=SUM(R[-" + Trim(Str(LastSumRow - FirstSumRow + 1)) + "]C:R[-1]C)"

Open in new window


Below sub will make the sum formula in columns A-Z in row 51
Sub MakeSum()
    Dim FirstSumRow As Long, LastSumRow As Long, FirstColumn As Integer, LastColumn As Integer
    FirstSumRow = 10
    LastSumRow = 50
    FirstColumn = 1
    LastColumn = 26
    ThisWorkbook.Worksheets("Sheet1").Range(Cells(LastSumRow + 1, FirstColumn), Cells(LastSumRow + 1, LastColumn)).FormulaR1C1 = "=SUM(R[-" + Trim(Str(LastSumRow - FirstSumRow + 1)) + "]C:R[-1]C)"
End Sub

Open in new window

0
 

Author Closing Comment

by:upobDaPlaya
ID: 39933426
Never had used resize before..thanks for the tip..
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now