Solved

Adding a range in Excel

Posted on 2014-03-12
5
147 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 22

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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