VBA Formula To Repeat Down Rows Until Nothing In Cell
Aug 27, 2013
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
View 2 Replies
ADVERTISEMENT
Aug 11, 2009
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
View 9 Replies
View Related
Oct 28, 2008
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.
View 4 Replies
View Related
Apr 1, 2009
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.
View 14 Replies
View Related
Jul 7, 2009
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....................................
View 6 Replies
View Related
Feb 27, 2009
I have a 34-page workbook. On Every Row (starting on line 4), and on Every Page (except the first "Summary" page) is repeated, this same formula....over and over again in column "N":
=IF(A4<>"A",(IF(AND(A4<>"A",OR(J4="",J4="NT")),"OK","WARNING: EXECUTION NOT EXPECTED OF LOW PRIORITY TEST CASE!")),IF((LEN(J4)=B4),"OK","WARNING: STATUS DOES NOT EQUAL TC COUNT!"))
So, since this keeps repeating over and over again, is there some way (either by formula, macro, or whatever), that I can prevent the constant repeating on every line, to bring the file size back down? [Note: This formula alone actually added almost 5MB to the file size!]
Two things to know:
- The formula above is seen on row 4 (that's why you see "4" all over the place). On Row 5, it would be "5" instead, and so on...
- The formula above always starts on row 4, and goes down to the "last used" row, in column "N" (so there's other information in that last used row as well).
....hopefully this is a very easy thing to do?
(PS: Thanks PeterSS for the help creating the above formula in the past...hopefully it can now be made less repetitive to conserve ~5MB of file space!)
View 10 Replies
View Related
Jun 11, 2008
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
View 3 Replies
View Related
Jan 14, 2013
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
View 8 Replies
View Related
Apr 3, 2014
Code is trying to
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
View 4 Replies
View Related
Feb 27, 2014
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
View 10 Replies
View Related
Jun 28, 2014
I have raw data stored in one row which I would like to break up into four individual rows.
I cannot get the formula to repeat and I don't want to manually do it.
View 3 Replies
View Related
May 24, 2007
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?
View 4 Replies
View Related
Aug 18, 2007
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.
View 6 Replies
View Related
Jan 5, 2009
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.
View 7 Replies
View Related
Jul 6, 2007
This macro is recorded, but I need it to start on row 2 and repeat down to row 3000. What changes do I need to make.
View 13 Replies
View Related
Feb 18, 2013
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
View 3 Replies
View Related
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!.
View 12 Replies
View Related
Jan 30, 2014
I have data in rows like this
A B C
1 x y z
2 x y z
3 x y z
4 x y z
5 x y z
6 x y z
7 x y z
8 x y z
9 x y z
10 x y z
11 x y z
12 x y z
What I am looking for is that I want to repeat the rows after every third row in another column. For eg
A B C D E F G H I
1 x y z x y z x y z
2 x y z x y z x y z
3 x y z x y z x y z
View 1 Replies
View Related
Sep 12, 2009
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.
View 3 Replies
View Related
Sep 3, 2008
In the excel sheet bellow that I'm attaching you will see what I'm looking for.
View 8 Replies
View Related
Jan 24, 2009
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.
View 3 Replies
View Related
Jul 10, 2014
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!.
View 11 Replies
View Related
Dec 15, 2008
How do I keep repeating
If Range("E22").Value Range("G3").Value Then
Rows("22:22").Select
Selection.Delete Shift:=xlUp
Until E22 does = G3
Then I need it to go to Row 23 and do the same.
My goal is to leave only rows (between rows 22 and 50) that have the same value in its column E that match cell G3.
View 9 Replies
View Related
Jun 9, 2014
I'm trying to create a formula (if possible) that will repeat column headers down a row and repeat every nth time. I have text values in cells E6:AL6 and would like to use a formula that will make it so that E6 will show up B2, F6 will show up in B3, G6 will show up in B4, etc. And then when it gets to B35 (AL6), B36 will then revert back to E6, and the loop will continue - B37 will be F6, B38 will be G6, etc.
My thought is to create an offset/address of some sort that will read as OFFSET(E6,0,x+1) and if x>35, start back at 0. However, I don't think you can create such a formula using an offset?
View 5 Replies
View Related
Feb 19, 2010
I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?
My formula is in cell K7 is:
=IF(AND(K24<=0%,K24>=-2%,OR(J32="T",J32="F")),1,"FALSE")
Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:
=IF(AND(R24<=0%,R24>=-2%,OR(Q32="T",Q32="F")),1,"FALSE")
and then to automatically continue like that for cells Y7, AF7, AM7, and so on.
View 6 Replies
View Related
Mar 7, 2013
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
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
View 5 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 Replies
View Related
Feb 4, 2009
I have a column of text strings in a2.a??? this is my data. Each text string has a persons name somewhere in it with other text around it.
In column c2.c??? i have text names of people names im searching for. eg. bob,jane,harry
what would be a pratical use of the vlookup function to return the name in column b2.b??? if the name from C was located in the string in A this is a non case sensertive requirement.
View 9 Replies
View Related
Jul 8, 2009
I am in the process of developing a budget template for next year and here is what I want to accomplish through Macro to avoid the tedious mannual manipulation process (hope that's achievable). BTW, I am only an entry-level Macro user who has no background in coding.
I have attached an Excel sample but let me explain:
In each of the expenses tabs (1, 2, and so on (only 1, 2 given in the sample)), I want to be able to select a range of the data cells (A19:Q34 in "Expense 1" and A10:Q28 in "Expense 2") and run this Macro so that:
1. 10 blank rows be inserted between selected data rows
2. A sum created for each month from the four rows below) with different color
3. A Year to Date Actual and To Year End Forecast created based on the VLOOKUP of the "P&L" matching the account code by month
4. Finally grouping of the first 9 rows.
the above is what I have manually created for the first account code in the "expense 1" tab. I have 7-8 expenses groups and about 200 account codes. So mannually creating the above for each expense groups is not that terrible if this will make life easier for Macro.
I would think this is a very typical Macro issue but can't really come up with anything.
The other issue I ran into the VLOOKUP for the YTD Actual and TYE Forecast. It is to look up accont code in the "P&L" tab and match the #s. But the column numbers in the VLOOKUP do not change automatically when I copy across. Thus I have to change mannually, which is really a pain in the butt. I also know I can't copy down because that will change the account code that I want it to match. Because to be able to copy the VLOOKUP formula across the same account code, I need to use the $ to fix the account code. But to copy down I think I need that to be without the $ sign. Any solution on that?
View 9 Replies
View Related
Mar 31, 2014
I wish to create a spreadsheet which has formulas which would enable me to see if a patient has had more than one referral created within the next 30-90 days [the actual number of days not important - the ability to modify the formula to reflect which time period required would be cool] and also to see if a patient has been referred for the same medical condition again. Thus in my mind at least two different formulas - has the patient been referred again within a time period of days and has the patient been referred again for the same medical condition.
Attached is an example referral spreadsheet I have created
View 4 Replies
View Related
