Solved

Adding a range in Excel

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

18 Experts available now in Live!

Get 1:1 Help Now