×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Macro - Formulas
2

Excel Macro - Formulas

Excel Macro - Formulas

(OP)
In my macro I'd like to put a sum formula at the end of the column, but within the column there are blank cells and since the spreadsheet has constantly # of rows there is no static range I can input in the macro to capture all of my data.  Is there a macro command to use to capture an "ever changing" range for a formula?

Thanks for your help!

RE: Excel Macro - Formulas

2
Hi Yvette,

Please read the following excerpt from a help column (I don't remember which): It may be helpful
----
I have an Excel worksheet with data in cells A1:B10 plotted on a chart. Each day I add another row of data. How can I make the chart range grow automatically, so that tomorrow it is charting A1:B11, the following day A1:B12, and so on?

Let's assume you're working in Sheet1 with column headings in row 1 and days in column A. Data relating to each date is in column B. Using Insert | Name | Define, type each name in the Names in workbook area, and then type the corresponding formula in the Refers to area. Click Add and type in:
Days=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
PlotData=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

Click on the series in your chart so the =SERIES formula appears in the formula bar. Edit the second and third arguments in the formula, replacing the reference to the column A range with the range name Days and the reference to the column B range with the range name PlotData. The result should be something like this:
=SERIES(Sheet1!$B$1,Book1!Days,Book1!PlotData,1)
Now the chart will automatically grow as you add data.
-----------

Good Luck!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources