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.
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.............
I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.
I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say Cell A2 has a value 20 Cell A3 has a value 3 Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
I need to insert one or two rows depending on the criteria of two different columns. We have two shops (A and B)...and the sales are expresed like this: ....
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:
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.
I need to insert blank row/s between the missing dates and fill the missing date & corresponding employees name. Let me explain in detail,
I have a start date in Cell E1 and end date in Cell G1. Need a macro to verify the date range in Col A with these start & end dates and insert blank row for missing date corresponding to employee names in Col B. This should be repeat till last used row of the sheet. Attached sample(Before&After) workbook.
Both missing date and respective names to be filled in the newly inserted rows.
I have a macro that runs in Sheet 1 and enters the word "Complete" in cell P of the active row. The same cell that requires the word "Complete" in Sheet 2 is in cell T of the active row. Is there a way to have the same shortcut that will identify which worksheet it is in and go to the appropriate cell?
I need to insert blank row/s between the missing dates. Let me explain in detail,
I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.
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.
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?
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).
I require spreadsheet to insert row based on the Value in Column (C). If The value in column C is < 15 then insert Blank Row below the line.
find attached the speardsheet.
i.e. 01.00am has been booked for 14plt which is LESS than 15 therefore i require excel to insert row so i can book addtional delivery during that hour.
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
I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).
The Data looks like this:
Column B Column C 12543 2 13456 2 19543 1
I want it to look like this:
Column A Column B 12543 12543 12543 13456 13456 13456 19543 19543
I have lists of numbers in column A. Based on the number in the cell, I would like to insert RO_ or RP_ before the number. I currently have two buttons for this, one named RO and the other RP and have simply recorded a macro of what I wish to do. However, instead of inserting RO_ or RP_ before the number the macro replaces the hole lot with "RP_10" or "RO_10" as this was the cell i recorded the macro on. How do I make it so it just inserts the Prefix rather than replace the contents of the cell.
Currently my code for one of the buttons is as follows;
Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.
For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.
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.
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.
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.
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.
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.
There's got to be a way to do this (mail merge style perhaps...) I would like to insert the entry/ies under a heading in a table in one worksheet whenever that heading matches the data entered in a separate worksheet.
I have a data which i need to total based on document types.
Document types in cells are AB=CELL A1, ZR=CELLL A2, ZP=CELL A3, SK= CELL A4.And amounts in rows 555=CELL A1, 625 = CELL A2, 725= CELL A3, 925=CELL A4 and so on.
My challenge is to get all the total of ZR,SK,AB type of documents using macro.Initially i used this formula, however now document types are more than one.
I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
