How To Repeat Process For Rows

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


ADVERTISEMENT

Select Multiple Files And Have Macro Repeat The Process

Sep 7, 2012

Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.

The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!

How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.

VB:
Sub openandcopy()

Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)

[Code] .....

View 4 Replies View Related

Repeat Rows By Mutiplying Current Amount Of Rows By Cell Value

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

Process Of Adding More Rows Has Slowed Down Considerably

Nov 15, 2009

I am building an inventory list for an online store with Excel 2002. Everything was fine until I reached 10,000 rows and 50 columns.

I use formulas to convert wholesale price to retail price on about ten columns.

Overnight the process of adding more rows has slowed down considerably.

I had my computer checked out and they say it is working just fine.

View 9 Replies View Related

Convert 1 Row Of Data To 4 Rows And Have It Repeat Every 4 Rows?

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

Add Numbers From 2 Rows,then Move To Next 2 Rows & Repeat

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

Copy/past Rows From Sheet1 To Sheet2 But Only If - Automate Process?

Jun 24, 2009

I have two sheets: Sheet1 - linked from external file, new data coming daily.
Sheet2 - I need to copy/past Rows from Sheet1 to Sheet2 but only if B-cells are not empty. How can I automate the process?

View 2 Replies View Related

Repeat Rows At Top Except One

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

Add Two Spreadsheet Rows And Repeat

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

Macro To Repeat On All Rows, Not Just The First One

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

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!.

View 12 Replies View Related

Repeat Values In Another Column After Every N Rows

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

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 View Related

F4 Does Not Repeat Insert Rows Or Columns

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

Copy & Repeat Single Row Information To Many Rows

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

Copy Each Row Of Table & Repeat Rows X Times

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

Drag Down Numerical Value / Repeat 2 Rows And Perform These 2 Tasks In Any Row

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

Repeat The Following Rows And Formula With Column A Starting At 9150

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

Insert Rows & Repeat Values Based On Corresponding Cell Value

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

Adding Rows With Control Number That Only Repeat 3 Times: Formula

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

Reformat Table Layout To Repeat Rows Based On Cell Values

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

VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

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

Modify Macro In Excel / Transpose - Delete Contents - Skip X Rows - Repeat?

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

How To Highlight Rows Or Data If Data Repeat In Complete Row

May 28, 2012

How to highlight rows or data if data repeat in complete row.

View 3 Replies View Related

How To Process Checkboxes In VBA

Feb 11, 2014

how to handle checkboxes in VBA

I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1

what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.

what do I need to declare or set or ? to access the state of the checkbox

View 6 Replies View Related

Process Before Printing

Feb 2, 2010

I need to print a worksheet. The sheet has rows of a specific height (45) and data in one cell grows - adding comments/notes with date attached so that last one entered is displayed, the rest get shoved down.

I need to sometimes print out the sheet. However, I want to have all the notes shown so would like to (1) autofit all rows, (2) print the sheet and then (3) put all rows back to row height of 45. All this I would LIKE to have done by "trapping" the print button on the normal Print area

I have attached some code, but the sheet (1) does not print at all, (2) the code is sometimes executed twice (in debug mode followed it) and (3) sometimes does not set the row height properly either at autofit or static height.

View 2 Replies View Related

Automate Process

Feb 22, 2007

Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.

Example:
A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months

what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.

Could this be done by date formula?

View 9 Replies View Related

Log Macro Process

Jun 30, 2006

I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)

For example:

Sub MyMacro1 ()
code
End Sub

Sub MyMacro2 ()
code
End Sub

If this process was logged, then the log file would look something like:

Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub

But it would be a in text just like a log file.
Is this possible with Excel ?

The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.

View 4 Replies View Related

VBA Process For Multiple Actions

Jul 17, 2009

I have a list of petroleum accumulations with three types of data: Reservoir depth, Net Pay and Gas-oil ratio. for each accumulation, the number of reservoirs within each can vary. So I may have a accumulation with only one set of those stats, or an accumulation with 20.

I need the program to go down an alphabetized list (8000 entries long) and after every unique accumulation name average the values of the stats(which are in three separate columns) then put it in one row perhaps beginning in Column M or another sheet. So that I have a list of accumulations with the average of those stats, one entry per accumulation name.

View 5 Replies View Related

UserForm Won't Process Because VBA Is Too Long

May 9, 2014

I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.

When I tested it, I hit the submit button but it says the procedure is too large!

Here is the code which is repeated 143 times but with a different row number and range each time (in red):

[Code] .......

So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.

The range is always going to increase nice and easily: C5 up to C146.

The row number is a bit hit and miss.

Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved