I have a sheet of around 5000 rows, I would like a macro to do some manual work for me.
Is it possible for a macro to insert a row if a "1" is present in column I. If possible I would like the row to be inserted above the 1.
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 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: ....
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?
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:...................
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 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.
I need a simple VB Code which will check the vale in cell A11 in a closed workbook.
If A11=Lunch insert a blank row otherwise do nothing
i need a macro for inserting a row in sheet by criteria. the Criteria:
1-checking if Collumn G ( letters ) are equal
if not then inserting the row
2-cheking if collumn H ( numbers ) are equal
if not then inserting the row
one more thing i need the row to be yellow from collumn B .. to .. I
I have data in cells B2:E2 and this can go down 100+ rows.
In column B i have invoice numbers but some cells contain the word "Deposit".
I have sorted this data so that the invoice numbers appear first and then all the Deposits.
I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedI have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
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
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.
TIME
DELIVERY COMPANY
PLTS
REF
[Code]...
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 am trying to figure out how to automatically copy and insert rows based on the QTY number in column D. For instance, rows 2 and 3 are already correct since QTY=1, but since row 4 has a QTY of 5 I would want the formula/macro to then insert 4 rows above row 4 and paste the exact data that is in row into the inserted rows. I would want the macro to loop and do this for all the rows in the sheet until there are no rows with a QTY>1...
View 9 Replies View RelatedI'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 a cell (B4)that may contain several different text values, i.e. Bill, Tom, John, Mike, Larry, & Dan. The value in cell D4 needs to relate to what text is in B4, i.e Bill = 6; Tom = 12; John = 8; Mike = 20; Larry = 15; & Dan = 10. I was trying an IF statement, but having trouble.
View 2 Replies View RelatedI need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:
In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'
Here's image of before and desired after:
Before:
PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here
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 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;
[Code] .....
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
X GREG 23.55
GREG 84.21
GREG 8.69
X MIKE 83.50
X SETH 41.33
to look like:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
[Code]....
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 have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies View RelatedI 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 4 Replies View RelatedI want to insert rows based on the coloumn F (has 6.00 on it)
206101.08.200901.08.20096.00
Since the coloumn F has value 6 on it, i want to insert 5 rows below the current row
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.