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).
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
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.
View 4 Replies View RelatedI 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 2 Replies View RelatedI am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
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 6 Replies View RelatedColumn 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.
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....................................
I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
I am on office 2010
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
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A
B
C
D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
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]....
I 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 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 have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................
With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
the example above would then become abca cabc
I have to create 1000 dynamic ranges and do not want to do this manually. See the list below for the syntax I am using, I am only showing the first 10.
Name Range
KPI_01=OFFSET(Date,0,1)
KPI_02=OFFSET(Date,0,2)
KPI_03=OFFSET(Date,0,3)
KPI_04=OFFSET(Date,0,4)
KPI_05=OFFSET(Date,0,5)
KPI_06=OFFSET(Date,0,6)
KPI_07=OFFSET(Date,0,7)
KPI_08=OFFSET(Date,0,8)
KPI_09=OFFSET(Date,0,9)
KPI_10=OFFSET(Date,0,10)
I have tried the following code which works to an extent...
Range("=OFFSET(Date,0,1)").Name = "KPI_01"
..but the final result of the named range is an actual range, i.e. "A1:A13".
How can I keep the named range as a dynamic range?
I would like to insert rows into column A from column D based on the count values and without affecting subsequent values in column B and C. For example. Count of a value in column A =1 and count of that same value in column D=5, THEN 5-1 (count of D - count of A) insert 4 rows in column A (shift cells down) without affecting data in column B AND C. Basically making the counts in column A and D equal.
COLUMN A
COLUMN B
COLUMN C
COLUMN D
G250001
[Code]....
The above example should look like this -
COLUMN A
COLUMN B
COLUMN C
COLUMN D
[Code]....
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:
1) Select and Copy entire row 2 from sheet(1)
2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable.
3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3.
4) loop through all records until complete
I have tried writing the code in VBA but am not even getting close!
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 have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
I have a program that automatically exports info into excel as that info becomes available. What I need is to be able to insert into a cell the exact time that the information was inserted into excel. For example, cell A1 gets info inserted into it, I need cell B1 to automatically insert the time that the info was inserted into A1. Any time there is new info inserted into A1, B1 needs to update that corresponding time.
View 2 Replies View RelatedI want to put a permanent date & time stamp in cell A1 (date) and cell A2 (Time) when cell A3 is populated by any character which would be an inputters initials.
I'm thinking that it will be an "IF" statement but then I am getting confused about how I make it not update when the spreadsheet is opened at a later date and time.
I have a set of 3,000 data points. The data is in one second intervals, however I am interested in seeing what the data looks like in a graph if I only had a point every minute. I have the following set up.
A B C
TIME X Y
What I want to do is to leave the first cell unhidden, and then hide the next 59. And then repeat this so I narrow the data down to one second each. I'm looking to do this multiple times, so I can create a graph each time in order to get an idea of what the data would look like if I only had a point for every minute.
Is there a macro that I could write, that would hide every cell except every 60th one? I was thinking that I could hide it based on the time. What would the code look like if my time was in 00:00:00 format? It's increasing, so I need it to hide cells that aren't equal to XX:XX:01, for example.
Here is my table which is just a test sample of the larger table, but in the end, it is column 1 I want to base the new ws on.
Before Macro
AY *AB1UnitCount2533210431 LGR SQ10584631 LGR SQ7726 Excel tables to the web >> Excel Jeanie HTML 4
The code below works fine through the first instance of the match and adds a new ws based on the name, but when it gets to the second match the macro tries to add the ws all over again and I get a run time error 1004 which states you can not add a ws and name it the same as one that already exist. I only have one sheet in my wb titled "AY". How can I also have the two column headers transfer to the new ws?
Public Sub CopyUnit()
Dim N As String
Dim i As Long
Dim ws As Worksheet
Set ws = Sheets("AY")
N = Worksheets("PAS Codes").Range("L14").Value
For i = Range("A65334").End(xlUp).Row To 1 Step -1
With ws
If Cells(i, 1).Value = N Then
.Rows(i).Copy
Sheets.Add.Name = N
Rows("1:1").Select
ActiveSheet.Paste
End If
End With
Next i
Application.CutCopyMode = False
End Sub
After Macro
31 LGR SQ *AB1UnitCount231 LGR SQ7331 LGR SQ10 Excel tables to the web >> Excel Jeanie HTML 4
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
