Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macros that will work when aditional data is added
I want to write macros that will automatically update the spreadsheet when I
add further data. For example I have 10 columns x 8 rows of dat and write a macro to generate average and standard error of the mean in the cells below each column. Later, when I get the results from the next set of experiments I insert a further 8 rows of data giving me 16 rows in all. Is it possible to construct a macro that will handle this or do I just have to insert the formula again this time for 16 rows? This may sound trivial but we have lots of data accross many different experiments and it would be more elegant and efficient if I could produce a spreadsheet that our students could just paste their results into that would do the necessary calculations for them. -- medjpb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macros that will work when aditional data is added
Supplying the code you have at the moment to perform your task would be a good start, we can then build on that and give you the help you need! John;333521 Wrote: I want to write macros that will automatically update the spreadsheet when I add further data. For example I have 10 columns x 8 rows of dat and write a macro to generate average and standard error of the mean in the cells below each column. Later, when I get the results from the next set of experiments I insert a further 8 rows of data giving me 16 rows in all. Is it possible to construct a macro that will handle this or do I just have to insert the formula again this time for 16 rows? This may sound trivial but we have lots of data accross many different experiments and it would be more elegant and efficient if I could produce a spreadsheet that our students could just paste their results into that would do the necessary calculations for them. -- medjpb -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=93219 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macros that will work when aditional data is added
A macro works the same as a worksheet function. SAuppose you have a range
from A1:A10 and you put in cell A11 the formula "=sum(A1:A10)". Adding a row at row number 5 will change the formula now at A12 to "=Sum(A1:A11"). The problem is if you add the new row at row number 11 the formula will move to A12 but the formula will remain at "=sum(A1:A10)". What I usually will do is add a new row at row 10. This will move the fromula to A12 and update the formula to "=Sum(A1:A11"). The problem is the data at A10 is at A11 and the cell A10 is empty. I then copy A11 to A10 and then put the new data at A11 to put the new data after the old data. The same thing applies if you are adding multiple rows. "John" wrote: I want to write macros that will automatically update the spreadsheet when I add further data. For example I have 10 columns x 8 rows of dat and write a macro to generate average and standard error of the mean in the cells below each column. Later, when I get the results from the next set of experiments I insert a further 8 rows of data giving me 16 rows in all. Is it possible to construct a macro that will handle this or do I just have to insert the formula again this time for 16 rows? This may sound trivial but we have lots of data accross many different experiments and it would be more elegant and efficient if I could produce a spreadsheet that our students could just paste their results into that would do the necessary calculations for them. -- medjpb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macros that will work when aditional data is added
Use a loop to get the last filled row.
OR The below will give you the last filled row in Column A. lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row If this post helps click Yes --------------- Jacob Skaria "John" wrote: I want to write macros that will automatically update the spreadsheet when I add further data. For example I have 10 columns x 8 rows of dat and write a macro to generate average and standard error of the mean in the cells below each column. Later, when I get the results from the next set of experiments I insert a further 8 rows of data giving me 16 rows in all. Is it possible to construct a macro that will handle this or do I just have to insert the formula again this time for 16 rows? This may sound trivial but we have lots of data accross many different experiments and it would be more elegant and efficient if I could produce a spreadsheet that our students could just paste their results into that would do the necessary calculations for them. -- medjpb |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up macros that will work when aditional data is added
Put the data into a List (Excel 2003) or a Table (Excel 2007). When data is
added just below a list or table, the list or table expands to include the added data. Formulas that refer to whole columns in lists and tables keep referring to the entire column even as the column changes its number of rows. Alternatively, put a blank row below the tabulated data, color it yellow or gray so it looks different than a data row, but include the blank row in formulas that analyze the column. Train the lab monkeys to insert one or more rows above this shaded row and put their data into the inserted rows. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "John" wrote in message ... I want to write macros that will automatically update the spreadsheet when I add further data. For example I have 10 columns x 8 rows of dat and write a macro to generate average and standard error of the mean in the cells below each column. Later, when I get the results from the next set of experiments I insert a further 8 rows of data giving me 16 rows in all. Is it possible to construct a macro that will handle this or do I just have to insert the formula again this time for 16 rows? This may sound trivial but we have lots of data accross many different experiments and it would be more elegant and efficient if I could produce a spreadsheet that our students could just paste their results into that would do the necessary calculations for them. -- medjpb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding an aditional forumla to this one | Excel Worksheet Functions | |||
Excel File Slowing Down after Macros Added | Excel Programming | |||
Chart that will expand to aditional rows | Charts and Charting in Excel | |||
macros won't work when tabs with data used are hidden | Excel Programming | |||
adding aditional taxes | Excel Discussion (Misc queries) |