Move Range Of Cells Based On Cell Criteria
Nov 16, 2007
I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.
Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub
View 7 Replies
ADVERTISEMENT
Sep 7, 2009
I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules.
The rules are as follows:
1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date.
2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract).
I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................
View 9 Replies
View Related
Apr 1, 2014
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?
View 9 Replies
View Related
Feb 20, 2009
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View 3 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
Aug 12, 2009
I've been trying to figure out how to use a formula that would enable me to copy/copy & move a range of cells right one column if a certain criteria is met.
Basically, if a criteria is met, I want to 'copy' everything from a row to another row, but offset it by one column (so that what was in, let's say, a1 would be in b2).
I've been tinkering with index(), if() and offset() in various combinations but think I must be missing something.
View 5 Replies
View Related
Oct 14, 2013
I have an excel file that contains data from bank transactions.
In column A at irregular intervals is a cell with " User Group:" It depends on how many transactions there are in a batch, could be 1 or up to say 200 as to when the next "User Group" cell is found.
After that also in column A is "Item" then "Lodgement Ref" then at the end "Batch Totals"
i.e.
User Group:
Item
Lodgement Ref
Batch Totals:
User Group:
Item
Lodgement Ref
Lodgement Ref
Lodgement Ref
Batch Totals:
I want to find each instance of "Item" then move that cell and every cell over to column CK down to and including "Batch Totals" i.e. A3401 to CK3410 to CM 3400 so it lines up with the top of the transaction block.
There are over 60,000 lines so it's worth trying to find a solution as I can then use the methodology to make other refinements in the spreadsheet.
View 9 Replies
View Related
Sep 27, 2013
I have the following data
Country
Aug-13
Sep-13
Oct-13
Nov-13
UK
3
5
3
2
USA
10
14
16
13
France
1
5
7
9
What I would like to do is sum a specific range of this data based on three criteria. Each criteria is in its own cell.
- Start Date
- End Date
- Country
So if I wanted to sum from 'Sep-13' to 'Nov-13' for 'USA', the formula would give me the answer 43.
View 4 Replies
View Related
Nov 8, 2007
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this:
Sub testmove()
Dim c As Long
For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5
If Cells(c, 6) = "Closed"
Rows(c).Cut
End If
Next c.......
View 3 Replies
View Related
Jul 21, 2007
Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"
I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B
View 3 Replies
View Related
Jan 10, 2012
Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".
Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1
[Code] .........
View 9 Replies
View Related
Jan 3, 2014
I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?
Creating Macro that automatically moves row to another spreadsheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
View 1 Replies
View Related
May 2, 2007
Here is the situation:
There is a spreadsheet that I use that has multiple plans in one column for various members. You can see the plan for Robert James and Amanda James is plan 5. If all this information was in column A how would you move the plan type say column C for each member?
For example
Column A
Customer Name
Robert James
Amanda James
Plan 5
Nikki Martinez
James Gross
Plan 6
View 4 Replies
View Related
Mar 19, 2008
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.
this is what I have come up with....
sumif(N10:N49, "Business", AW10:AY49)
but it returns incorrect values and not the whole range????
Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...
H2 and H3 are my attempted formulas
View 3 Replies
View Related
Jan 9, 2007
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
View 9 Replies
View Related
Mar 20, 2009
I want to copy cells A&B where there is a value in G to a new workbook. I need to loop through the workbook as there are 25 sheets in the workbook, and there 8 workbooks to go through.
There are 31 rows on each sheet, and there are only a few values in column G on each sheet.
A is customer name, B is Account No, G is Insurance premium paid, columns C to F, and H to M have other accounting data in them.
The idea is to create a global list of all customers who have already paid their insurance premiums.
View 9 Replies
View Related
Sep 19, 2009
Here’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
View 10 Replies
View Related
Mar 4, 2008
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!
View 5 Replies
View Related
Oct 24, 2006
At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.
The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).
View 9 Replies
View Related
Jun 28, 2014
I have multiple sets of data that i need to reference based on the value in another cell. In this instance I am importing data to Excel from an outside source, this data is not consistent each time I import it and therefore based on the values that are imported I need to reference different cell ranges. Here is an example:
Import Data
A B
Alex 1
Ben 2
April 2
Harry 3
Data Sets
Alex Data Range
1 2 3
Refine Help Token
Cover Safe Coin
Simple Aware Change
Ben Data Range
1 2 3
Sorry Away Candy
Happy Home Soda
Mad Neutral Water
My problem is that based on the name in column 1 of the import data I need to reference that data range so I obtain the correct column of Alex's data range with a random chance of any item in the column to be selected. Currently my formula looks like this: =INDEX(CELL("contents",A1),RANDBETWEEN(1,3),B1) and it is returning #VALUE, however when I put the Range Name "ALEX" in in place of the "CELL" formula it works properly the problem is I am not able to put the correct name in for each row.
View 2 Replies
View Related
Mar 21, 2008
I would like to change cells A2 to G2 to a different colour based on the value of G2
(0 to 48) Dark Green
(-2) Light Green
(-4) Yellow
(-6 to -48) Red
The range of the sheet would be A2 - G130. Each Row would change on value of cells in Column G.
View 9 Replies
View Related
Jun 6, 2012
I have two rows of numbers, say D1 to F1 and D2 to F2. Row D2 to F2 will always have lower values.
The range below it is D3 to F100. I want the range to clear the contents of the cells in the range where:
a) the cell is > the x1 cell above it
OR b) the cell is < the x2 cell above it
If it is = or between the two cells, the value stays.
Example:
If D1 thru F1 is 4, 5, 7 and D2 thru F2 is 2, 4, 3, then:
D3 is 5, it is cleared
E3 is 4, it stays
F3 is 5, it stays
D4 is 2, it stays
E4 is 1, it is cleared
F4 is 9, it is cleared
Etc
Basically, the cell in the range looks up its own column, compares itself to x1 and x2 in that column. If it's greater than or less than, then it clears the contents, if not, the value in the cell stays.
View 3 Replies
View Related
Jul 1, 2008
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following:
Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View 9 Replies
View Related
Feb 4, 2014
I have a sheet which I need to arrange and it looks like -
Column A
Column B
Column C
Column D
[Code]...
the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"
Output should be -
Column A
Column B
Column C
[Code]....
so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.
View 9 Replies
View Related
Jan 31, 2009
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).
Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.
This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.
View 9 Replies
View Related
Sep 12, 2012
I'm creating a project calendar in Excel and for each task I have a Start Date (A) and End Date (B) in adjacent cells. To the right are corresponding date cells like a timeline set up with 52 cells representing weeks of the year. I.e Week 1 (C) starts at 02 Jul 12, Week 2 (D) starts at 09 jul 12 etc. up to 24 Jun 14 (BF).
Start Date
End date
02-Jul-12
09-Jul-12
[Code]......
When a start date and end date are entered in the respective columns, I need to have the color start on the cell for that range in the timeline and that color should carry out to the end date cell in the timeline. A bit like a Gantt chart but not to that detail.
View 2 Replies
View Related
Feb 17, 2013
I'd like to shade a range of cells based on a particular cells value.
Specifically:
If cell E3 is JUNE then I'd like the range of cells from B10 thru I10 to be shaded.
If E3 is JULY then it would shade C10 thru I10....etc.
Excel 2010
View 9 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Jul 2, 2008
Is there other syntax that can be used other than:
Range("A23").Select?
I'm trying to move to any cell below some text. So, it will find "Total" and move the cell below.
Also, is there any way to specify to select cells without specifying the actual cell name?
View 9 Replies
View Related
Oct 24, 2013
I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:
1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.
2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.
3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.
View 5 Replies
View Related