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
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 ..................................
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?
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.
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'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.
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.
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.......
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
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
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
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
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.
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)
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).
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.
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.
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.
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.
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.
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.
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
