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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
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
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 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
VBA For Insert Previous Month In Sheet Name On Open
I'd like to make a template so that when it is opened the previous month name is inserted in the sheet name. The reason I want to use previous month is that normally this report is created in the month following the month being reported. So it is opened, months are updated, then the user saves as xls. Ideally I guess sheet would not have the month on the template. The name of the sheets are shortened months like "Dec Results Bob" and there are 8 of these. I can tell it might start: Private Sub Workbook_Open() (or would .xlt be the same?) but that's about it. I made a macro of renaming sheet but that didn't really tell me if I could insert the Month there.
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
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 .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
Insert Rows Based On A Value In A Cell
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
View Replies!
View Related
Insert Rows Based On Cell Value
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
View Replies!
View Related
Add/Insert Row Below Cell If Value Matches
I have a column O with the heading "Return Visit Required". Each cell, starting in row 5, has data regarding a job. So each row represents a different job. If the cell in a certain row in column O has a value="Yes" then I need 1 row added below that cell. If the value="No" or "" then do nothing. I need the value "Yes" to be non- case sensitive.
View Replies!
View Related
Insert Date In Cell In Each Visible Row
I have a spreadsheet from which i run monthly reports for aother business area. I have filters in row 2 (which is my header row) and what i want to do is filter column CH to show blanks and filter column CE to show non blanks. Then in every visible cell in column CH below the header row (row 2) i want to enter todays date - this is so a record is kept of when each row was detailed in the report. I have tried the code below (which i tried to amend (unsucessfully) from code i got here to clear some cells when i ran another filter for another report). Sub FilterSheets_Monthly_OFMDFM() If ActiveWorkbook.ReadOnly = True Then MsgBox ("This filter must add the date on each row included in this return." & vbLf & vbLf & "It must be run while the workbook status is not Read Only!" & vbLf & vbLf & "Please close this spreadsheet and re-open using the password. Thank you.") Exit Sub End If Dim i As Integer Dim rng As Range Application.EnableEvents = False Application.ScreenUpdating = False........................
View Replies!
View Related
Insert Rows Based On Cell Time Value
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time. For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View Replies!
View Related
Insert Cell Comments Based On Value In Another Workbook
I need a macro that will do the following scenarios. 1. I have two workbooks. a. The first contains a sheet with " Names" listed in column A. b. The 2nd contains a sheet with "Names" listed similar to first workbook. The only difference is that the 2nd workbook contains 2 other columns, "Age" in B and "Address" in C. 2. Only the 1st workbook is opened. 3. When I click a name in the 1st workbook, it will look for a match with that of the 2nd workbook. a. If there's a match, it will copy the corresponding "Age" and "Address" and display them as comment to that name in the 1st workbook. b. Else, it will display a comment that no record was found.
View Replies!
View Related
Pasting To Previous Row
I need to paste Entire row in one sheet( Sheet1) to the previous row in other sheet(Sheet2) ex: If i copy 3rd row from one sheet(sheet1) than it must be copied to 2nd row in other sheet(sheet2) Sub try() Sheets("Sheet1").Select Range("b2").EntireRow.Copy Sheets("Sheet2").Select Range("b2").EntireRow.Previous.Select ActiveSheet.Paste End Sub
View Replies!
View Related
How To Insert Blank Row Below A Cell Containing Specific Text
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text. For example: - All of my data is in column A -I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
View Replies!
View Related
Insert Cell To Columns Having X Blank Cells In Row
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
View Replies!
View Related
Insert Formula Based On Cell Entry Using Vba
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3 i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View Replies!
View Related
Insert Number Of Rows Based On Cell Values?
Column B contains geographical Areas. Column C contains a list of business departments. North Env North Env North Ops North Sales North Sales North Sales South Env South Maint South Ops South Sales South Sales South Sales Etc. The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View Replies!
View Related
Insert Rows & Headings Based On Cell Value
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
View Replies!
View Related
Carry Over Data From Previous Row
Attached is a sample of excel file, its a simple spreadsheet that suppose to track a usage of items that were taken out of inventory, but at the same it needs to be printer friendly (that's what's causing all the problems), it got messy when I had to carry over from previous row, I had to manually enter under "Quantity ch." in order to have correct amount under "Left" column, (see row #4) I think I can eliminate whole "Carried over" column if I create a formula that will enter data into B4 only if J3 has a number, I also need to copy the same formula's down each column,
View Replies!
View Related
Insert Rows & Repeat Values Based On Corresponding Cell Value
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above. For example - before macro: Joe Bloggs 2 Adam Wilson 10 Peter Andrews 0 Claire Burrows 6 After macro: Joe Bloggs Joe Bloggs Adam Wilson Adam Wilson Adam Wilson Adam Wilson....................................
View Replies!
View Related
Next Question To Be Based On Previous Answer
I am trying to write an excel sheet that has multiple questions but need to be answered in order and the next question in the series answers should appear based on the answer to the previous question. expample: Question 1: Steel or Aluminum Question 2: (If the answer was Aluminum) 3000psi or 3300psi (If the answer was Steel) 2640psi or 3442psi or 3500psi Question 3: If the answer to #2 was 3000psi: 50,63,80 If the answer to #2 was 3300psi: 100 If the answer to #2 was 2640psi: 66,85,95,98,104,112,125 If the answer to #2 was 3443psi: 80,100,120 If the answer to #2 was 3500psi: 65,80,100,120 I want the choices for the next question to be hidden before the previous question is answered and the choices based on the answer to the previous question. Would love to be able to use a drop down box to do this with.
View Replies!
View Related
Return Value Based Upon Previous Returned Value
We have a product plan, which we are trying to automate. To break it down we have a product number which will consist of several other components parts. In the product plan we are tying to automate showing which components are in constraint to the side of the complete part number. We've tried using VLOOKUP but it only ever returns the first value it finds, whereas we need to show all constrained parts. Have tried to break it down into ranges as per attached, but this is unreliable as the data is drawn from another source, and corrupts the ranges if re-imported or sorted.
View Replies!
View Related
Adding Data By Row Based On Typing In Cell Contained In Row
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more. Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc. Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
View Replies!
View Related
Identify Button (shape) Row To Insert Row And Delete Row
I have a button (group containing and add and delete button). I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row). Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row. This will allow me to add/insert rows by the button located at that row The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp) I also note that when a group is copied, it has the same shape name as that copied. I want to keep the add and delete shape within the group (BtnGrp). I do not want to select a cell or row or enter a row number to delete etc. refer to sample workbook attached. Currently only has one record row.
View Replies!
View Related
Return T If Any 2 Of The 3 Values From Previous Row Are Double Digits
Need a T/F formula that will return T if any 2 of the 3 values from previous row are double digits and the third digit is also from previous row. The 3 digits in the same row must have a zero. Examples 668 T 806 995 T 509 995 F 519 ******** ******************** ************************************************************************>Microsoft Excel - CASH 3 EVE COMBO GROUPS.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDE302/06/09006T402/05/09016F502/04/09110T602/03/09610F702/02/09661T802/01/09160FSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Copy Formula From Previous Row When Data Is Entered
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours. I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60. I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.
View Replies!
View Related
Format Row Based On Single Cell Value In That Row
I have a macro that's supposed to see if cell 5 meets criteria and continue to all sheets in workbook, except " Total". When I activate the macro on a specific page, it runs smoothly on that page. But when it's finished, it doesn't continue to the next sheet in the workbook. Sub ColourBG() Dim ws As Worksheet Dim line As Integer endline = Range("A1000").End(xlUp).Row For Each ws In Worksheets If ws.Name <> "Total" Then With ws For line = 3 To endline Application. ScreenUpdating = False If (Cells(line, 5).Value = "0206") Then _ Cells(line, 1).EntireRow.Font.ColorIndex = 5 '*(Blue) Next line Application.ScreenUpdating = True End With End If Next ws End Sub
View Replies!
View Related
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color. Attached is an sample of what i want.
View Replies!
View Related
Changing Font Color Based On Change From Previous Value
I want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black. How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?
View Replies!
View Related
Insert Row Syntax - Resize Row VBA Error
I need to insert rows in row A44 depending how many cells exits in a range i named "ALL_C" I saw this code that inserts 10 rows on A44 so i thought by modifying it the way i did would work but it didn't. I'm still new at this stuff. What am i doing wrong? Original Sub Insertinrow43() Range("A44").Resize(10, 1).EntireRow.Insert End Sub Modified (doesn't work) Sub Insertinrow43() Range("A44").Resize(Count(All_C), 1).EntireRow.Insert End Sub Thank you!
View Replies!
View Related
Insert Range-named Row At User-selected Row
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet. If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position). This is what I have tried so far:
View Replies!
View Related
|
TRACKER
