Solved

Adding a range in Excel

Posted on 2014-03-12
5
148 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

689 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