Insert Row and Formula

I tried really hard to find this myself, but failed.

I have a worksheet containing 5 separate blocks of data (own headings, 
column titles, etc); some of the cells contain formula.

I want a macro that wll insert a row when a block is filled ( in column "H" 
actually), copying the formula as well.  This needs to work on all the blocks 
when the same condition is met.

Can anyone help me?

Many thanks
David


0
Utf
11/30/2009 5:27:02 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
854 Views

Similar Articles

[PageSpeed] 34

You should try turning on the "LIST" feature for each of your datasets. Put 
your cursor in the data and press CTRL-L to activate the "List" wizard.

Once it is on, you'll see an "expansion row" offered at the bottom of your 
data. If you type ANYTHING in that row, Excel will expand the "LIST" to 
encompass this row permanently and it will copy any formulas from the row 
above into that row as well as formatting.

This will also work if you click on a row in the middle of the data and 
select "insert row". The "List" will always make sure new rows have the 
needed and formatting and formulas. 

-- 
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Dav1d" wrote:

> I tried really hard to find this myself, but failed.
> 
> I have a worksheet containing 5 separate blocks of data (own headings, 
> column titles, etc); some of the cells contain formula.
> 
> I want a macro that wll insert a row when a block is filled ( in column "H" 
> actually), copying the formula as well.  This needs to work on all the blocks 
> when the same condition is met.
> 
> Can anyone help me?
> 
> Many thanks
> David
> 
> 
0
Utf
11/30/2009 10:47:02 PM
Reply:

Similar Artilces:

Is there any Excel trick to highlight a bunch of rows and then make it as if I had used the control button to highlight them all?
The Visible cells only control works great for making it possible to ditch the "control" button while highlighting rows. But what if you have within these rows 3 or 4 times when the rows are contiguous? This is happening to me. Each row in my subtotaled spreadsheet needs to be separately highlighted as if I had used the control button on all the rows. I then use a border button on these rows. This works great when the rows are discontinuous ...I'm only highlighting "total" rows on my subtotaled spreadsheet, but one example where there is no disconnect between a tot...

Can't keep focus on second row of subform
Hi all. Hope you can help me with this question. I have a form with two subforms on. The main subform is in datasheet view (named KTable), while the second subform is in continiuos form view (named KNotes). In the parent form, I have a text box named ID. The text box has the following source: =[KTable].controls("ID") The KNotes subform is linked to this ID text box. This construction will display the related rows of KNotes when I select a new row of KTable. So far everything is OK. The problem is when I want to change some of the values in KNotes. If KTable.form.orderby is ...

Need to select rows to average based on a value in a different column
Below is an example of my spreadsheet. I'd like to find the average number of users for each "Day of the week" hour combination but I'm not even sure where to start. Date Hour DoW # Users 10/27/2003 10 Mon 11 10/27/2003 11 Mon 11 10/27/2003 13 Mon 10 10/27/2003 14 Mon 11 10/27/2003 15 Mon 9 10/27/2003 16 Mon 9 10/28/2003 8 Tue 5 10/28/2003 9 Tue 6 10/28/2003 10 Tue 4 10/28/2003 11 Tue 8 10/28/2003 13 Tue 7 10/28/2003 14 Tue 7 10/28/2003 15 Tue 7 10/28/2003 16 Tue 10 10/29/2003 8 Wed 7 10/29/2003 9 Wed 10 10/29/2003 10 Wed 11 10/...

How to display inserted pictures with conditions?
Hi All Is it possible for me to write a code to display an inserted picture o my choice to only display when the value of a cell changes to what require for the picture to appear? I would really appreciate any hel provided. Cheer -- Message posted from http://www.ExcelForum.com Hi have a look at the following suggestion http://tinyurl.com/yulak -- Regards Frank Kabel Frankfurt, Germany > Hi All > > Is it possible for me to write a code to display an inserted picture > of my choice to only display when the value of a cell changes to what > I require for the picture to app...

How I can switch row/column in pivot chart?
Hi, I have the new office 2007, in excel I try to draw pivot chart, I succeded. But I want to switch row into column and column into row. When I try to do that tis button bocomes invisible while I can do in PPT. Please help me!! Regards, Tuba Kesen Umar Pivot charts in Excel 2000 through 2007 only allow you to plot series in columns. However, you could rebuild the pivot table so that your old column fields are located in the rows area and vice versa. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "kesen...

How do I Time stamp individual rows in excel?
I have to use excel to update inventory. I don't know what formula to use. What I need to have is every time a record is added, a seperate column will put a time stamp there. I tried useing the NOW() command, but all the records changed when I added data in a row. Please help. "lost and confused" wrote: > I have to use excel to update inventory. I don't know what formula to use. > What I need to have is every time a record is added, a seperate column will > put a time stamp there. I tried useing the NOW() command, but all the > records changed whe...

How do I make each row add up seperatly? Please make it easy...
I have never set up anything on excel before. My job has always had it ready for me. I want to do this to help me figure out how much I will need for my baking ingredients for the holidays. I thought that I could put a row for sugar, and then for everything I am making with sugar type in 3/4 cup and 1 1/2 cups then at the end of all the sugar items have it add how much I will need. Can anyone tell me step by step how to set this up?? Please make it easy I am not computer smart at all. I'm willing to take this project on! Why don't you email what you have to ng@officear...

pasting row to another workbook prompts because same defined name exists in new book
I have a set of weekly spreadsheets, all the same except for the dat that is entered onto them. What we ned to do is cut and paste lines from last weeks workbook int this weeks one. The problem is I have set up data validation on one o the columns based on a list of names, and so now it prompts ever singl time we try to paste into the new workbook, because the same define name 'tradelist' is in the new sheet. is there any way I can stop this prompt somehow because tis driving u mad and taking up time clicking yes or not (when it makes no differenc if we click yes OR no because its e...

year formula 04-13-10
I'm sure this is very simple, but for some reason it's not working for me. I have a log of training sessions provided to clients. I want to run a pivot table that totals the number of hours for the year. I track the date in the spreadsheet, so I wanted to add a column of "year" so that I can easily get totals per year. I want to reference the date column to populate the year for that date (I have hundreds of lines already so I don't want to do it manually) When i added the column & used the year function, it comes up with 1905 each time instead of...

Excel Formula needed
12864 decreased by 3% for 19 years. I would like the decreasing value in the fist column and the difference in value in the second column. as an example: 12868.00 0000.00 12478.08 385.94 Thanks in advance In article <W74rn.10625$Ek4.7339@newsfe24.iad>, joemocasanto@aol.com says... > > 12864 decreased by 3% for 19 years. > > I would like the decreasing value in the fist column and the difference in > value in the second column. > > as an example: > > 12868.00 0000.00 > 12478.08 ...

formula #30
hi all i want to write formula for one of my excel column, but it`s complicate. my formula has 3 if in if ! what should i do? -- y.ranjbaran On Mar 7, 8:48=A0am, y.ranjbaran <y.ranjbaran.99d1...@excelbanter.com> wrote: > hi all > i want to write formula for one of my excel column, but it`s complicate. > my formula has 3 if in if ! what should i do? > > -- > y.ranjbaran You need to provide a few more details please if we are to help you. 'trip_to_tokyo[_3_ Wrote: > ;1558546']On Mar 7, 8:48*am, y.ranjbaran > <y.ranjbaran.99d1...@excelbanter.com...

how to insert bar in margins to id. changes made to existing doc.
I just need to know how to insert a vertical bar in the margins to identify a change that has been made to an exisiting document. Thanks You don't mention what version of Word you are using (always useful) but for Word 2003 and prior, Tools, Options, Track Changes, Changed Lines, change to Outside Border. In 2007 look in the Word Options under the Microsoft Office button Hope this helps DeanH "cmoulton" wrote: > I just need to know how to insert a vertical bar in the margins to identify a > change that has been made to an exisiting document. >...

#VALUE error add'g formulas
Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a “#VALUE” result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis –– but I need to display just one result for both rows - in the “Row 2” cell of the “HOURS WORKED” column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that &...

How to insert new pop3 settings in curent user outlook environment via script or command line?
I have environment of 5000 + computers & I need to set up additional pop3 acces on users environment.. I have tool that reads custom data & I want to assign pop3server, login, snmp server data as new account in curent user environment, since my tool operates via command line I would like to use some script that can do such job. Targeted version is ooutlook 2003 Regards. "Ram Demon" <globalmapleplayer@gmail.com> wrote in message news:%23Ro6ciuoJHA.6132@TK2MSFTNGP06.phx.gbl... >I have environment of 5000 + computers & I need to set up additional pop...

Excel 2003 formula
I'm trying to make a formula for a list to check and strike out another on 3 sheets. Only 2 are using the info, sheet 3 has various colums to check. Sheet 2 A B dog yes cat (blank) Sheet 3 A C E etc... Dog(striked) Blank Dog(striked) If sheet 2, B is yes then check Sheet 3 colums and strike them out or change color. Hope that made sense. Similar to a called off list. Please help me if you can. TIA Ginger If you are familiar with conditional formatting you could apply conditional forma...

How I do this formula ?
I would like to format all words that said �customer� in a column to chosen color.Can anyone provide a formula or conditional formattin that will do this -- HR15 ----------------------------------------------------------------------- HR157's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3445 View this thread: http://www.excelforum.com/showthread.php?threadid=54371 You could apply an autofilter to the column and select Custom | Contains then enter customer. This will only display those rows which contain customer in that column, and you can highlight the cells...

windows mail crashes when signature is inserted into emails
Whenever I write an email and try to insert my signature before sending, WindowsMail crashes and closes, and I get the following error message: "Windows Mail could not be started. The application was unable to open the Windows Mail message store..... Close the program or wait for it to complete its operation, then open Windows Mail again. (0x800C0155)" I tried creating a signature and asking it to "always insert" the signature, but whenever I tried to create a new signature, it crashed as well. We ran the wmutility program yesterday and still no success. ...

Create summary tab of last entry/row on all tabs in workbook
I am tracking projects in a workbook. I have a tab/worksheet for each project. For a weekly status meeting I create a spreadsheet that has the last entry/row from each tab. Is there a way to program so I do not have to copy/paste from each tab each week? I do add and delete tabs/projects from time to time. PS- not very adept at the technical details so need to respond to me in simple terms : ) You might want to submit your question to an Excel NG since I don't see anything in your question regarding MS Access. IMHO, I would move the workbooks to an Access database rather than...

How to omit certain cells in formula
Hello If I have monthly data in a table, but data form April on is not available yet, but I would like to be inputted automatically from some other table when it becomes available there. So I put in the cell a formula =DATA!I49. And as the source is empty so far- I get the result of the formula '0'. But at the end of this line (that now includes data till March and for the next 9 months there are 0) I have formula for average- which by now also takes in to account the 0 in the line- which I do not want. What would be the best way to tell the excel that: calculate average in the lin...

how to copy&paste range with formulas which are without frozen cells ($) ?
hi, I've created a monster - huge reporting file, it takes lot of inpu data and play with it in many ways in many workseets to create m monthly 16page Production control and logistics report(6 divisions european curriences, fx rates, delivery performance, and lot more) a its very big I can coordinate cell links anymore. My problem is: i have range with formulas, and result of those formula im useing in God knows how many other formulas etc. I want to chang those cells slightly so everything will suck new results, but i want t keep also old calculation somewhere(next to it) if: -I copy a...

increment of field after insert working poorly
Access 03 I have a sales quote form with line item numbers. I have a macro that increments the item number (using dcount) when a new item is added. It's tied to the After Insert event of the form. When the macro runs, it leaves the record unsaved when it's done. If I attempt to add a SaveRecord line, an error results that tells me I can't use the BeforeUpdate event (which is maddening, because I'm not using it). I don't like leaving the record unsaved, because the user my click the save record button twice. bicyclops wrote: > Access 03 > > I have a sales q...

Re: delete row based on conditions #4
Hello Bob, My requirement as below Assembly Pos Prepart a 100 b a 101 l a 102 k a 191 c a 192 f a 193 y a 200 d a 291 g a 300 m a 370 n a 371 w a 400 i i would like to have some indication against position numbers 100,101,102,193,200,370,371 so that i can delete those rows For a particular position number (n00), if any of the following pos extensions exists n0...

INSERT INTO Table, values derived in code from separating a name
Hi, I have a field called [Contact Name] which is the result of a combo box. On not in list the user is asked if the name they enter is too be added to the list. The name is separated into two strings "str1l" and "str2" in code form a module as the function is called up from the not in List on the Combo box. str1 is FIRST_NAME str2 is LAST_NAME The fields in tblCUSTCONTACTS are "FIRST_NAME" and "LAST_NAME" Now I wish to INSERT into the tblCUSTCONTACTS the two strings. strsql = "INSERT INTO [tblCUSTCONTACTS] " _ ...

Emailing from Excel based on date selection by row
Hi I need help with this code which should check all filled rows in the sheet and compare the date in D(i) with todays date in "H1" and if = send an email. The email part works OK and the code was taken from a forum . But the parameters MailSubj1 and Mailsubj2 values are not passed to the SendNotesMail subroutine. Can anyone help with this? Here is the code: ------------------------- ooo ------------------------------ Sub checkdate() Dim Ws As Worksheet Dim oRow As Long Dim Mailsubj1 As String Dim Mailsubj2 As String Set Ws = ThisWorkbook.Worksheets(&...

Counting Rows/Columns for Copying Formulas
I have two spreadsheets in a workbook as follows: Source Worksheet contains by customer each product it purchases and the sales for each product (see sample below). Not all customers buy each product. There are also new customers added/deleted each month and new products added/deleted each month. Column A Column B Column C January February..... Customer A Product Code 1 Customer A Product Code 1 100 Customer A Product Code 2 200 Customer A Product Code 3 300 Customer B Product Code 1 500 Customer B ...