?
Solved

Adding a range in Excel

Posted on 2014-03-12
5
Medium Priority
?
152 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 1000 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 24

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 1000 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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