Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
VBA Macro To Insert Horizontal Page Breaks Based On Criteria Of 1 Column
I want to achieve is a procedure that inserts horizontal page breaks at certain parts of the sheet where there is a cell equal to 2. Here is the code I have so far. Sub insert_pagebreak() Dim printbreak_cell As Range Dim j As Long Dim i As Long ActiveSheet.ResetAllPageBreaks Set printbreak_cell = Range("AD1") j = 1 For i = 1 To 100 If printbreak_cell.Value = 2 Then Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell j = j + 1 End If Set printbreak_cell = printbreak_cell.Offset(1, 0) Next i End Sub Everything works until the cell value reaches a 2, and then once it goes into the If statement I get a 'Application-defined or object-defined error' at the below line. Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell.............
View Replies!
View Related
Insert Row Macro Based On Text String
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler The Data Begins in Row 5 of Column A My Current Macro is as below which I have adapted from another one I used It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
View Replies!
View Related
Insert Next Row Based On Previous Cell Value
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.
View Replies!
View Related
Vba To Copy And Insert Row Based On Cell Value
vba to copy and insert row based on cell value I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example, Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet. I would want to copy and insert the entire row. LABELAB110 2 1
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Insert A Row At End Of Set Criteria
I have a spreadsheet detailing account numbers (there are more than 1 of the same account number) that I currently filter by text (contains) and an additional filter to sort from A to Z What I would like for my next step (I am looking to create this as a macro) is insert a row when one account number stops and another starts eg: A/C SS501 SS501 SS501 SS503 SS503 SS503 I would like it to read SS501 SS501 SS501 SS503 SS503 SS503 etc.. Without having to insert row manually.
View Replies!
View Related
Macro Code: Find Cell Or Row Multiple Criteria
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example: Sheet 1 contains source data: Date Name Time Complete Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
View Replies!
View Related
Insert Rows Based On Criteria Match From Sheet2
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example: Sheet1 looks like this:...................
View Replies!
View Related
VB Macro For Variable Cell Merge Into HTML Code Based On Criteria
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code? The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number). I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
View Replies!
View Related
Insert Row Based On Condition
I would like to write a code, that can check the name of cell A1 with A2. If A1=A2, then insert a blank row between these two rows. I have started with a loop, but somehow it does not work. Sub AddRows () Dim Row As Long For Row = 1 To 3800 If Cells(Row, 1).Value = Cells(Row + 1, 1) Then Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If Next Row End Sub
View Replies!
View Related
How To Hide/undide A Row, Based On A Cell Value (Macro)
I have the data as follows: columns B,c,d,e,f,g,h contains data and column A is empty. What i want is 1. Initially columns c,d,e,f,g,h should be hidden. Only a & b should be visible 2. if i enter a 0 (zero) in A1, then, columns B,c,d,e,f,g,h should remain hidden, for row-1. 3. if i enter a 1 (one) in A1, then, columns B,c,d,e,f,g,h should be visible, for row-1.
View Replies!
View Related
Insert New Row With String Based On Conditional Statement
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell. Eg: Active cell is something random like E16 I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
View Replies!
View Related
Insert Row Based On Data Validation List Choice
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List. Exampl of data: Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught". Column K is where the Data Validation List is located with a in cell drop down. What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row. This would be so I can keep a historical of programs and dates taught. And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
View Replies!
View Related
Identify Row Number Based On Value In A Cell And Use That Row Number In A Macro
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use. Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE) I have attached a scaled down version of the Workbook. Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
View Replies!
View Related
Insert Row Macro
I would like to find the code to make a button in excel, that when clicked, will insert a new row into the last row of a section so that the formatting is the same as a sample of other rows in that section, but it will be clear of data and only contain the correct formatting. I would also like this code to make a sum formula in a cell below the newly inserted row to change and to include the new cell within its formula. For example: I have a form on excel that has space for 6 rows of data with 3 pieces of information (which are: item, section, cost). If / when I need to add a 7th line I have box that I would like to assign a macro to so that when I click on it, it adds a 7th line with the same formatting that can be found in rows 1-6 but will be void of data. However, under the 6th row I have a total of the 'cost' with an '=sum(D7:D12)' formula and any other code I have found and have used it does not automatically tell the formula to add an additional cell to its summation to make it =sum(D7:D13).
View Replies!
View Related
Insert Row Using Macro
I hav a macro to insert a new row. But i also want the macro to ask the user to input where he wants(which row number) to insert the new row?? For example when i run the macro, an inputbox should come up asking to input the row number. The user should be able to enter the row number (take 10 for now). And Excel should automatically insert a new row in row number 10. So the previous row 10 now becomes row 11.
View Replies!
View Related
Macro To Insert A New Row
how to automaticly create a new row below the last data entry. I have a list of items with relevant data in each row. But below all the data I have statistics of total percentages of certain things. I want to keep that at the bottom of the list. When I add another row for more information I want those statistics to jump down a row so I have more blank rows to add in more data. Is there an easy way to do that? Also something kinda related. I know you can freeze rows and columbs at the top and left of the spread sheet, is there any way I can freeze those statistics at the bottom so they are always visable but the data on top of it can scroll through? I dont want to split the page cause that kinda causes more problems.
View Replies!
View Related
Insert Or Delete Row Using Macro
I have columns 1 to 5 that requires user input. At the beginning of first row, i have created two buttons +R to insert row and -R to delete row. I used macro recording to get this done. It works well but I need both buttons to be copied on for other rows as well. Let me re-explain, when a person clicks on +R in row 10 I need a new row inserted at row 11 which both the buttons +R and -R copied in. When a person clicks on -R at row 10, i want row 10 to be deleted. the first default should never be deleted. All rows should be added before statistic as statistic row will calculate all the inputs from user start to finish. Can this be done? 123456+R -R Statistic: PLS refer to this diagram 12345+R,- R Statistic: ignore this diagram I even tried http://www.mvps.org/dmcritchie/excel/insrtrow.htm but it is not working. I am not sure where I am doing wrong.
View Replies!
View Related
Out Of Control Insert Row If Macro
I am trying to build a macro to insert a row or two rows under an existing row if a formula returns a '2' or a '3' in the first cell of the existing row. This is what I've come up with so far and the first bit works fine but when it comes to inserting the rows it just spirals out of control, inserting never-ending rows. Private Sub Labels1_Click() Columns("A:A").Select Selection.Insert shift:=xlToRight Range("A3:A2000").Formula = "=ROUNDUP((Q3/300),0)+1" With Sheets("Sheet1") ' Test Condition 1 For Each c In Worksheets("Sheet1").Range("A3:A2000") If c.Value = 2 Then c.EntireRow.Select Selection.Insert shift:=xlDown End If Next c
View Replies!
View Related
Insert Row Next To Macro Button
I've got some rows containing formulas under one of several header rows. I'd like to insert a button in column A of the header row which will insert rows below that header. I've got it working using absolute and relative references, but don't know how to determine where the button is located in order to insert below the right header row. Hopefully that makes sense. If needed I can attach a sample spreadsheet. Current macro Sub Test() ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select Selection.Copy ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False End Sub
View Replies!
View Related
Run Macro Upon Row Insert
I feel like this is textbook to the point of being in FAQ but couldnt find it, so if the answer indeed exists in FAQ - notify me of the url and i will refer to that instead. But my question is fairly simple, I'd like to have a macro run upon the insertion of a row.
View Replies!
View Related
Insert Row And Copy Cell From New Row
Attached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View Replies!
View Related
Insert A Row With A Macro On A Moving Table?
I'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table. I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.
View Replies!
View Related
Macro To Find Data And Insert Row
I've got a problem that's causing me a headache. i have a document with 1 column and 4000 rows (its an export from active directory). I need to do a search cell by cell (so A1 - A4000) for any cells that contain the text "changeType". When a cell is found, i need excel to insert a new row above the current cell and insert the text "modify" into the new row (in the first column). eg. x x x changeType x x Becomes: x x x Modify ChangeType x x
View Replies!
View Related
Move Row Based Off Criteria
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this: Sub testmove() Dim c As Long For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5 If Cells(c, 6) = "Closed" Rows(c).Cut End If Next c.......
View Replies!
View Related
Macro To Insert A New Row With Incremented Serial Number
I need a macro to insert a new row with incremented serial number in column A, when done the first time - to insert the row at a specified cell starting with serieal no: 1 and later to insert the new row after the last row. Also I need the macro to insert certain pre-defined text values in specified cells in the new row..
View Replies!
View Related
Repeat Row Based On Text Criteria
to run a macro or any other methods to repeat the same head rows in my database as followings: Let's say the main head row is in row 1. Item names such as Item A; Item B; Item C ....in column A What I want is to insert the same head row after the list of each item. Item name (head row) Book A Book A Book A Item name (head row to be repeated) Book B Book B Item name (head row to be repeated) Book C Book C
View Replies!
View Related
Delete Rows Based On Two Criteria Within Each Row
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool. The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error). The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...). What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as: "T" "[" "T" "#" "T" "@" "T" "]" The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ). I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
View Replies!
View Related
Delete Row Based Upon Multiple Criteria
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this: -----Column A-----------Column B--- 1.-- Computer_X ------- Software_A 2.-- Computer_X ------- Software_B 3.-- Computer_X ------- None 4.-- Computer_Y ------- None 5.-- Computer_Z ------- Software_A 6.-- Computer_Z ------- None I would like to delete the entire row if: 1) Column B entry is "None" AND 2) Column B has entries other than "None" that match Column A So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A. Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
View Replies!
View Related
Macro To Insert Formula Based On Column Header
I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation. For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?
View Replies!
View Related
Combining Row Results Based On Specific Criteria
If I have the following table: Name ProductSize QuantityPrice per unit AX35510 AX451015 AX551210 BX601010 AX35510 I now want a resulting table that will combine all rows where name, produc, size and price are identical and have one row with the same data apart from the quantity that will have been added - so for the table above the resulting table will be: Name ProductSize QuantityPrice per unit AX351010 AX451015 AX551210 BX601010
View Replies!
View Related
Copy Row Data To Another Worksheet Based On Criteria
Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
View Replies!
View Related
Insert Column Macro Based Variable String Letter
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
View Replies!
View Related
Finding Data Based On Row & Column Criteria
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX Result data(criteria base on Month & type) JanApril BananaXXXX Apple Orange
View Replies!
View Related
Insert .jpg Based On Cell Value
I have some code that changes the colour of a cell based on the value of that cell, not sure how useful this is as I could just simply use conditional formatting. I have a pivot table that is constantly changing values, the column is a result of a number of days...not really relevant.... however If the cell contains a value of greater then 7 I want to insert a .JPG of a lil stop sign, if less then 7 then a little green go sign will be insertedThis is what I have so far..just changes cell color. Sub DoOnSelection() Dim oCell As Range For Each oCell In Selection If oCell.Value > 7 Then oCell.Interior.ColorIndex = 3 End If Next End Sub
View Replies!
View Related
Insert A Comment Based On The Value Of A Cell
I have data validation and conditional formatting set for my sheets. However, I cannot figure out something. I want a "comment" box (just as if I would click on "add comment") to pop up automatically if the user enters a certain value in a cell. For example, if cell C4="1" then no further action is required. If cell C4=anything other than "1", then the user would be required to enter a comment. I would also like the comment to autopopulate with the users login id. I will attach the book I am working on for reference, but I do not have any code written for this as I could not figure out where to start.
View Replies!
View Related
|
TRACKER
