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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Insert A Row And Add A Formula
I have a spread sheet that I need to insert rows into and then in those same rows, add formulas that display percentages compared to the grand total row. To make it a little more complicated, this spread sheet changes from day to day, so the rows and columns will be different each time. So the code will have to recognize text, then insert the row, then do the formulas in the blank row. The only constant will be that it will always start on Row 5. I have no idea how to start or begin to do this, so I attached a file with a page showing where I am at as an example, and then anther page showing what I want ....
View Replies!
View Related
SumIf Formula: Add New Data With An Insert At Row 13
The formula that works is =SUM(IF('Pipeline Input'!$X$13:$X$39=1,IF('Pipeline Input'!$H$13:$H$39="Lead",'Pipeline Input'!$K$13:$K$39,0),0)) I am trying to modify this formula so that the ranges are dynamic to allow me to add new data with an insert at row 13. What would the syntax of the formula look like if I use the INDEX function to allow the ranges to grow with new data? I have tried naming the defined ranges and entering the formula as =SUM(IF((CloseMo)="1",IF((SaleP)="Lead",(LoanAmt),0),0)) but I get a #VALUE! error
View Replies!
View Related
Delete Row If Cell Text Matches Known String
I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried: For i = 1 To 200 range("B" & i).activate If range("B" & i).value = " Total:" Then rows(activecell.row).select selection.delete shift:=xlUp End If Next i When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
View Replies!
View Related
Color Table Rows Where Specific Cell In Row Matches Condition
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C. Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row. Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""x""" Selection.FormatConditions(1) . Interior.ColorIndex = 16
View Replies!
View Related
Insert Cells Until Data Matches Up
I have posted in a existing thread at mrexcel as i believe my problem and possible solution is very similar to that already detailed there. However i have not recieved a response there yet and was looking to draw upon more minds here ...
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 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
Finding Last Row In Data Row That Matches Criteria
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.
View Replies!
View Related
Add A 3rd Lookup To A Formula With 2 Current Matches
a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1. Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file. I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3. Exp Sheet 1, report to be printed A1 = last name A2 = first name A3 = month Sheet 2, data is held here Need to pull data in column “M”
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
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
If Match Then SUM? - Wanting To Add Multiple Values If Criteria Matches
Column A would be Recipe Numbers, Column B the Planned Total. I want to search column A for all matching recipe numbers. Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match. Then the most difficult part, which just occured to me now. I only need the Total Planned Total and Recipe name reported once. Recipe#..........Planned Total 450................100 600................75 620................125 450................50 620................180 450................100 600................200 450................110 I will not be able to sort these lists. If-Match-Sum? to display: Recipe#............Total Planned Total 450....................360 600....................275 620....................305 I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this? How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.
View Replies!
View Related
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
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
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 Row Number That Matches 2 Criteria
I have a produced an Excel workbook which uses a VBA sign in/out userform. Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave. To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8. I need to have the row number returned for the IDnumber where the Out cell is blank. This should be the last occurrance of the IDnumber Example Row 1 ID# In Out 2 1 9:00 13:00 3 4 9:00 13:00 4 3 9:00 13:00 5 2 9:00 13:00 6 5 9:00 13:00 7 6 9:00 13:00 8 2 14:00
View Replies!
View Related
If Cell Contains Chosen Text, Add Value From Another Cell In Same Row
I have a sheet with 65,000 rows. Column c contains text and numbers (phrases from search engine results seraches). Column G contains the number of times the phrase was searched. I am wanting to run a formula / macro which goes like; If cell in column c contains variable then copy cell g out of same row to cell h. This way I can quickly sum up column h to see total number searches by variable. I have looked through forum for past half hour and could not find what i needed.
View Replies!
View Related
Return Matches & Non-Matches From Delimited Cells Against List Range
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working": 1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List 2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List 3.) Report In Column D: Identify and return all unique values in "Column C" as a List. Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View Replies!
View Related
Add Relative Row Formula To Cell
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2 Row 3 =A3+B3 However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
View Replies!
View Related
Add "x" If Customer Number Matches A List
I have 1) a list of 10,000 customer records with customer numbers (including duplicates) in field A, and 2) a separate list of 25 customer numbers that are "red" accounts. I want to add a new ID field to the larger customer list, and using the 25 red IDs, I want to mark any of the 10K records that match the list of 25 with an "x". I thought about using multiple nested-function formulas and concatenating the results, but then, I always do things the hard way! ...
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
Auto Insert A Row With Info Populated From The Row Above
to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g. We have 10,000 X Pens Customer 1 buys 1,000 Customer 2 buys 6,000 Customer 3 buys 3,000 i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row If this doesnt make sense then i am happy to answer your questions? i can upload an example excel sheet if required if someone can explain how i do this?
View Replies!
View Related
Add/Insert Missing Rows
can do the following thing: Here's a simplified form of my data under the two columns B and C B C 1 a 2 b 5 c 7 d I want dummy rows to be inserted for the missing rows like: B C 1 a 2 b 3 dummy 4 dummy 5 c 6 dummy 7 d
View Replies!
View Related
Go To First Cell In List That Matches Contents Of Active Cell
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution... I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust). I sort the list so that the all names that are the same (ie John Doe) are together. A short example list looks like this: Jeremy Apple John Doe John Doe John Doe Jimmy Kravitz Jimmy Kravitz In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it). Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on. Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View Replies!
View Related
Insert Row Then Copy Row Below
The posts I have found regarding inserting rows and copying formulas etc. and their answers do not seem to work in my worksheet. All I would like to do is insert a row then copy the row below into the new row. I have tried to modify the VBA given in similar posts and cannot seem to make it work.
View Replies!
View Related
Insert Row And Copy From Above Row
i have a excel spreadsheet named "ECN Number". in this sheet i have a row in which column5 marked with value "x",column22 is empty " " and column23 has "some value". if the above statement is true,i want to insert an entire blank row and i want to insert another row below this row below the empty row with column6 marked as "x",column22 empty " " and column23 with the samevalue from the above row. after inserting this row with these values, i want to insert another entire blank row. i have attached the workbook with the specified sheet.
View Replies!
View Related
Find Last Row Containing Value, Then Insert Row After
I am creating a worksheet of contacts and the date and time that I phone them, along with their current "status" in our company. I have created a form and have got it to do the insert of new data that I input, BUT What I would really like is enter a "client number" (which is unique) and have it search my table for any record(s) with that client number, and then show me the last record that matches the client number, (this can all be done from within my form) then If I click on the "insert" command button, I want it to insert a new row after the current row and move my form data in the form into the new row which will include a date and time. So, To summarize.....I need 2 similar subroutines. LOOKUP ----- I need to have my form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches, then display the data or display nothing is there is no match. INSERT ----- I need to have the form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches and then insert a blank row here and move my data into it.
View Replies!
View Related
Finding Sheet That Matches A Cell Value
I have worked on the code – with as limited knowledge as I possess – to perhaps communicate my intention. I know for a fact that in the code below there are 2 problematic lines – and herein lies the root of my problem. The first line is: “Dim Day As String” In Debugger speak, I am told that this cannot be done along with “Set sh = Worksheets(Day.Value)”. However, in normal speak, I am trying to copy data from one sheet to another. When pasting the data on the second sheet, I am trying to make the code find the appropriate sheet that matches a Day value in Cell I5. Getting the code to execute this one action is proving to be particularly difficult. I am thankful for any suggestion on how to get the code to work correctly.
View Replies!
View Related
Add Row Preserving Formulas And Formats From The Above Row
I have a table with 15 columns and 10 rows (initially all cells are blank but with formulas and formatted) and I want a way to add automatically a new row in my table each time something is entered on first cell in each row. For example if I enter something in cell A1 (first cell in my table) then automatically add 11th row and if I delete the value in cell A1 it is ok to leave the 11th row there but if I I go on and enter a vlaue in A2 then add row 12th. Of course I want the new rows to have the same formats and formulas as the previous ones.
View Replies!
View Related
|
TRACKER
