How To Drag Down Numerical Value But Repeat 2 Rows
Jul 10, 2014
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between . So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present:
X4 1
X5 2
X6 3
X7 4
X8 5
X9 6
X10 7
I need to have
X4 1
X5 1
X6 1
X7 2
X8 2
X9 2
X10 3
X11 3
X12 3
and so on
I have a 2000+ rows to manually input and hence it is not practical!.
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between, simultaneously keep the numbering in sequence only after the repeats 2 rows in between, and perform the previously mentioned 2 tasks any row in a column. So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present: X4 1 X5 2 X6 3 X7 4 X8 5 X9 6 X10 7
I need to have
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ] 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+1 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+2 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+3 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+4 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+5 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+6 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+7 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+8 3
and so on...
I have a 2000+ rows to manually input and hence it is not practical!.
I have a table which contains a list of branches eg;
01 02 04 06
I then have a spreadsheet which contains a number of columns and rows.
I want to create a new spreadsheet in the first column it will contain the branch from the table above then the rows and columns in the above spreadsheet
The rows and columns would then repeat for each of the other branches in the table. IE if I have 4 branches in my table and 100 rows in my spreadsheet my new spreadsheet would contain 400 rows
I have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61 A122 A183 A244 and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).
I have 3 columns and 200 rows. The first column is for Name of product. 2nd column for number and 3rd for $value of number of products. Have 200 rows. Have done a sort and checked. 2 sets of rows are for the same product name. I want to know if it is possible to add up values in 2nd and 3rd column, 2 rows at a time , then 4th and 5th column and so forth, by some formula rather than doing it manually?
Is it possible to repeat rows on top of every page but one? Specifically, I have a seven page spreadsheet and I don't want the rows to repeat on the seventh page.
I have a spreadsheet that contains traffic count data for a two lane road. The data is by direction, by hour, 24 hours a day, 365 days a year so the data is basically 2X 365 = 730 rows of data.
I need to simply add the two directions together for each hour of each day, basically add two rows together, drop down to the next two rows and add them together, repeat.
I need the result as stand alone data on another spreadsheet so the Data, Subtotals operation provided by Excel won’t work for me but something similar that puts the data on another spreadsheet would work.
I also tried to use a formula in the new spreadsheet that added two cells in two rows on the original spreadsheet together and then repeated that process a few times and then tried to drag the formula down but couldn’t get it to repeat correctly.
I have a list of rows with Yes and No check boxes. Columns "T" and "U" contain the output of these checkboxes (i.e. "TRUE" or "FALSE"). At the end I have a button which I'd like to run this code. Essentially, I want to code to go through row by row and do the following:
1. Check to see if both boxes are blank or if both boxes are checked - and if so set the background color to yellow 2. Otherwise set the background color to clear
I have written the following code which accomplishes this task for Row 9. Is there an easy way to repeat this process for rows 10-15 without copying all the text and changing the row numbers?
Then, any way, say "If all the rows (i.e. 9-15) have clear background colors (which would mean that they all 'passed' the test of having exactly one and only one box checked in the row), then run another macro which I have written"?
Rem Check to see if neither box is checked or if both boxes are checked and sets background color to yellow, otherwise sets background color to clear.
If (Range("T9").Text = "") And (Range("U9").Text = "") Or (Range("T9").Text = "TRUE") And (Range("U9").Text = "TRUE") Or (Range("T9").Text = "FALSE") And (Range("U9").Text = "FALSE") Then Range("B9", ("G9")).Interior.ColorIndex = 6 Else: Range("B9:G9").Interior.ColorIndex = 0 End If
how to get it to fill down. Basically depending on the contents of column D, a formula (which also needs to fill with the rows) is copied from sheet 2 in to column K.
Code: Sub Copy() With Sheets("sheet1") typ = .Cells(Rows, Count, "D").End(x1Up).Row
[Code]...
That's what I have. I am totally stuck. the formula needs to relate to column G and fill down too. this is the formula
HTML Code: =IF($G2=2,$G2*90,IF(AND($G2<4,$G2>2),"$270.00",IF($G2>6,360+(($G2-6)*50),IF(AND($G2>=4,$G2
My F4 key (which I use constantly) works for every "repeat" function except for inserting rows or columns. I can repeat every other option EXCEPT inserting rows/columns. I just had Office 2007 uninstalled and had Office 2003 put back on my PC. My IT group can't figure it out.
Need to sort my data in column A which contains a long list of numerical data. To do this manually using copy and paste would be easy, but not viable because of the large amount of data.
The attached PDF file illustrates the data. I wish to replace each date of dirth entry in column A (one D.O.B. in each cell / row, expressed as YYYYMMDD) with the client identifier number (format 937XXXX) a couple of cells above. And at the same time delete the row containing the client reference (or its contents). The data in column A should retain the same relationship to the data in the other columns (B,C). it may require a short piece of VBA code.
I'm trying to transpose multiple values in Excel, but I'd also like to repeat row values for columns A through E. The attached file "Raw Data" worksheet shows what I start with, and the "End Result" worksheet shows what I'd like the end result to be.
I would like it to scan an entire column (column E) for rows of data that fall into a range of numbers chosen by the user (like maybe a list box or combo box in sheet 1) for example a number greater than or equal to 8 but less than or equal to 15 and copy all of the rows and columns of data across the entire sheet that fit the range from sheet 1 to sheet 2. I would like to use a separate list or combo box for the min and max values in which to search as seen in the example sheet 1 attached. I have tried the autofilters, custom filters and macro recorder with no success as it seems the filters do not like ranges of numbers.
I need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
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'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.
1. Where there is data in column B 2. Goto first row of data 2. If column J = "Closed" 3. then select Cells from column B to J in that row and colour these cells in red 4. If column J= "Open" 5. the select Celss from column B to J in that row and colour these cells in grey 4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
=============================================================== Sub Colourclosed() Sheets("Risks").Select Dim LastRow As Long Dim i As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row For i = 8 To LastRow If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A 2. Skip the next cell/row (this is the company) 3. Select all the cells beneath the company cell, until it reaches the blank cell underneath 4. Copy, transpose these cells next to the Company cell (transpose in column B) 5. Delete the contents in row A that were just transposed 6. Find the next cell with data (company) 7. Repeat Steps 2-6
On the 'Total' sheet I'm wanting subsequent sheets to fill in on the rows. Do I have to redo the formulas for each row because it will correlate to a different sheet? I only know how to drag the formula down, but that doesn't seem to work right!
I have a workbook with five different sheets in it.The first is an inputs sheet where I populate inputs to my file. The rest all contain formulas in a top row (ex: A2:G2 in the first sheet D4:M4 in the second sheet...etc). These formulas are calculating values based off my input sheet and need to be dragged down the same number of rows as is in the inputs sheet (if there are 125 rows in my input sheet I need it to drag down the formulas 125 times, so from A2:G2 to A126:G126 or D4:M4 to D129.
The main problem is that I never know how many rows will be on the input sheet so the piece of code would ideally count and then apply that count to all of the other tabs and drag down the appropriate amount of rows. It is a base file so I save a copy each time I open so the start rows will always be the same.
