Insert Range Of Cells Based On Cell Criteria?
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
ADVERTISEMENT
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
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
Jan 29, 2009
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
View 2 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
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
Apr 21, 2009
I have a sheet of around 5000 rows, I would like a macro to do some manual work for me.
Is it possible for a macro to insert a row if a "1" is present in column I. If possible I would like the row to be inserted above the 1.
View 9 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
Feb 3, 2009
I need to insert one or two rows depending on the criteria of two different columns.
We have two shops (A and B)...and the sales are expresed like this: ....
View 9 Replies
View Related
Mar 12, 2007
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:
Sheet1 looks like this:...................
View 2 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
Jan 10, 2010
I want to achieve is a procedure that inserts horizontal page breaks at certain parts of the sheet where there is a cell equal to 2. Here is the code I have so far.
Sub insert_pagebreak()
Dim printbreak_cell As Range
Dim j As Long
Dim i As Long
ActiveSheet.ResetAllPageBreaks
Set printbreak_cell = Range("AD1")
j = 1
For i = 1 To 100
If printbreak_cell.Value = 2 Then
Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell
j = j + 1
End If
Set printbreak_cell = printbreak_cell.Offset(1, 0)
Next i
End Sub
Everything works until the cell value reaches a 2, and then once it goes into the If statement I get a 'Application-defined or object-defined error' at the below line.
Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell.............
View 3 Replies
View Related
Apr 26, 2008
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
View 4 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
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
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
Jun 19, 2014
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
X GREG 23.55
GREG 84.21
GREG 8.69
X MIKE 83.50
X SETH 41.33
to look like:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
[Code]....
View 3 Replies
View Related
Mar 12, 2008
I have an excel sheet that looks like this:
IMAGE 1
(The sheet holds more data but not needed for this)
I have two buttons; “BUY” and “SELL”
I need a vba-code that inserts either “BUY” or “SELL” in row 6 (buy/sell) and insert a text (code) in row 7 depending in this information. When I push either “Buy” or “Sell”
IMAGE 2
So.. When I push the button “SELL” (already made) the action “Switch” should get “Sell” and actioncode “SO”, and “Redem1” and “Redem2” should get “Sell” and actioncode “RA” and “RN”
So.. When I push the button “BUY” (already made) the action “Switch” should get “Buy” and actioncode “SI”, and “Subsc” should get “Buy” and actioncode “SA”
(When the button is pushed I have a autofilter that “hides” either all the “sell” or all the “buy”)
IMPORTANT: There is NO range!! the list goes on, and changes so I need it to work on x amount of rows. !
View 3 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
Sep 6, 2012
i am trying to insert a formula into a range of cells using a For... to loop
This is what it looks like:
VB:
For i=1 To n
Worksheets("Ret_sheet").Cells(i, 8).Formula = "=if(mid(B" & i & ",3,1)=""A"",""PY Campaigns"",mid(B" & i & ",4,3)"
Next i
The row with the formula returns a syntax error.
what the right syntax is? I have read a lot about inserting a formula in a cell using VBA, but i never met the case with a counter (i) usage. I guess the problem might be there.
View 8 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
Sep 22, 2009
I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.
View 3 Replies
View Related
Oct 22, 2013
what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?
View 6 Replies
View Related
Mar 17, 2014
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx‎
View 3 Replies
View Related
