If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Insert Column & Shift Left
I wanna shift a particular column (lets say column A) to the left, so I do the following: hojaFAC.Columns("A:A").Select Selection.Insert Shift:=xlToLeft hojaFAC is a worksheet BUT I get the next error when I execute my macro : RUNTIME ERROR 1004 Select method of range class failed Why do I get runtime error nº 1004 all the time also for other reasons. I think it´s the only runtime error I ever jumped into. But If I ever get a runtime error It's allways number 1004.
View Replies!
View Related
Insert X Rows After Specific Cell Value In Column
I have a dataset that i want to update, to do this I want to insert 8 rows after each row that has a value in Column C 2000. The set has 600,000 rows, comparing countries (cty, cty2) from 1980-2000. I want to make room for information up till 2008. I see lots of codes for doing this but I will also need help with actually inserting into my spread sheet.
View Replies!
View Related
Insert A Blank Column Next To Heading Columns
I am doing a lookup, populating a column (which works although slowly) then (in this part) doing a search on the header row.. If the heading contains the word "Category", I wish to insert a blank column next to (the column containing the heading), colour it yellow then look for the next heading and repeat if found. It appears to clear my first row so I must have my columns referred incorrectly as rows Dim FWord As String Dim i As Integer Dim lCol As Long Dim MyString As String . . FWord = "Category" lCol = Range("A1").End(xlToRight).Column For i = 1 To lCol Cells(1, i).Value = MyString '
View Replies!
View Related
Macro Insert Cells In First Blank Column
I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro.
View Replies!
View Related
Insert Blank Rows Where Date In Column D Changes
I have a spreadsheet containing hours logged against a specific project, all of which have a 'Week Beginning' date. I would like a macro to insert two rows above a change of date (in the D column). How best should this be done? It's basically a formatting macro I'm after, to group records by 'Week Beginning' date. I've spotted a few similar requests, but none of them appeared to include the function to check for a change in date, from the cell above.
View Replies!
View Related
Insert Blank Column And Copy/paste Values
I'd like to have done is to have a blank column inserted between columns W and X(these values change so the VBA statement should reference the end of the columns) and the values that are now in column Y(April 17th values) pasted as values into the now empty column X. I would like to do this for tabs Ann-Sheet 2. I'm having a bit of trouble with setting up the loop that would go through the desired sheets.
View Replies!
View Related
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1. A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
View Replies!
View Related
Find The Column Name Purple & Delete The Whole Column If There Are No Blank Cells
I'd like a macro that does 3 things.. 1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change. 2. Find the column named "Purple" (also by name for same reason) 3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
View Replies!
View Related
Show Sum Of Numbers In One Column IF Another Column Has A Specific Category
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E). So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.
View Replies!
View Related
Copy Non Blank Cells In Column Up 1 Row To Another Column
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish. Dim ii As Long For ii = lastrow To 6 Step -1 If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy***** Next ii
View Replies!
View Related
Insert Column, Move Another Column Into It And Autofill
I have attached a sample spreadsheet – what I want to do is insert a column before the current column A and move the current column D in to it and auto fill for every non blank cell in column B. Also, there are different values in column D such as “B03” ,”B41” etc – so for example, it should auto fill B03 until it comes across either a blank cell in column B or it comes across a “B41” and then it should do the same until it comes across a “B42” or a blank cell again. This needs to continue till the end of the report.
View Replies!
View Related
Insert Date From Column D, Into Column A, 2 Rows Up
Following up from Insert Blank Rows Where Date In Column D Changes from the first populated row after the inserted rows, and paste it in the 1st blank row above the populated rows (as a kind of title). How would I build this into the code below? Private Sub CommandButton1_Click() For x = Range("D65536").End(xlUp).Row To 7 Step -1 If Range("D" & x) <> Range("D" & x - 1) Then Range("D" & x, "D" & x + 3).EntireRow.insert Next End Sub
View Replies!
View Related
Specific Data From A Column
ive been writing a database for my business using excell and VBA I have worksheets for clients surpliers labour and parts i have written userforms that add parts clients and surpliers to their respective sheets I have started to white a userform that ties all this information together to create invoices it creates a new invoice number allows me to select which client the invoice is for and add labour to the invoice. I have a txtbox called "txtsearchparts" and a button called "cmdSearchparts" at this stage i can type a serialnumber into the "txtsearchparts" box and click the "cmdSearchparts" button. This saves the text that was entered into "txtsearchparts" as a variable called "partsearch". I am having trouble with the last bit. once the serial number has been entered , the button has been clicked and the serial number has been saved in "partsearch" variable, i want it to search the first column of the "parts" worksheet the the serial number contained in the variable and return the row address that the part stored on to a variable called partaddress.
View Replies!
View Related
Getting Rid Of Specific Column Data
In this example, i need to get rid of all the rows except for the mfg part #. The heading "mfg part #" can be deleted as well. The number of rows will vary each time i capture data. So for this example, i need 50 rows of data, but the next time i capture it, i might need to only keep 10 rows of data. The format will remain the same, the only thing that changes are the number of rows and the actual part #'s.
View Replies!
View Related
Use A Column Of Data If A Specific Value Is Found
I have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell. Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19. fig 1 Coloms A B C D Row A 5 5 6 6 Row B 5 6 6 8 Row C 4 5 6 7 Row D 5 4 4 5 Total 19 20 23 26 fig 2 Coloms A B C D Row A 1:00 2:00 3:11 4:01 Row B Row C Row E Row F 1:19 2:20 3:33 4:27 Row F-A= 19 20 23 26
View Replies!
View Related
Counting Cells In A Column With Specific Data
I want it to count and fill in a range in column A until it sees a blank or notices the change in value in column B. In the example below i hope it shows what i need to do. i left the last group without numbers to show that is where it needs to start counting over again. i am basically wanting to count down 1st place 2nd place etc.
View Replies!
View Related
Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in. I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
View Replies!
View Related
Search A Column Of Dates & Return Data From Another Column
I am trying to get a formula that will search one column range “B” and pull data from another, “D”. Dates are down column “B” and some of those dates are repeated several times. In column “D” there is only one piece of data (a number) entered for each day. Eg, if 08/06/2009 has been entered 3 times in column “A”, there will only be data entered in 1 of the cells of column “D” and blank cells in the other 2. Column B -- Column D 07/06/09 -- 54000 08/06/09 08/06/09 -- 62000 08/06/09 09/06/09 -- 61000 I couldn’t get the LOOKUP function to work properly, as there are duplicate dates in column “B” and I often got a result of 0 returned. As I’ve only got 1 piece of data added in column “D” per day and any duplicate days would just have blank cells in “D”, I can actually get a SUMIF function to work, SUMIF(B3:B60,DATE(2009,6,8),D3:D60). Although it does work, it doesn’t feel right using it and I would prefer a formula that would return just the one cell, instead of the sum of a range of cells.
View Replies!
View Related
How To Count A Column If It Matches Data From Another Column In Seperate Rows
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received. Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
View Replies!
View Related
Fill Month And Year Column Using Data From Date Column
I have 4 columns Column A is "Customer Number" Column B is "Date Can Opened" Column C is "Month" (currently blank except header row) Column D is "Year" (currently blank except header row) I would like a macro to do 2 things here.... 1. Extract the Month part of the "Date Can Opened" column and put it in column C. 2. Extract the Year part of the "Date Can Opened" column and put it in column D. Currently, the "Date Can Opened" column is formatted as a date like */14/01 taken from the Number tab in the Format Cells dialog box) ...
View Replies!
View Related
Autofilter Column A Then Copy Data To Another Sheet With An Empty Column
I'm trying to write a program for work. One sheet (whereiseverything.xls) will have a list of parts column E and where it is in the process column (k). Column E of this sheet will have multiple part numbers. (Sometimes duplicated.) I would like to figure out how to write a code to Auto filter (whereiseverything.xls) sheet and copy only one P/N from (whereiseverything.xls) column E onto another Workbook Worksheet (Commit status.xls) column. After that the sheet must copy all of the locations of that P/N from whereiseverything.xls column K into and under the P/N of the Commitstatus.xls. It will continue to autofilter and copy from where is everything, the "one" p/n and all of its locations into another empty column of Commitstatus.xls until it no longer has part numbers to autofilter on whereiseverything. I am extremely green on VBA programming but here is my first attempt. Windows("Where is everything commits .xls").Activate Windows("whereiseverything[1].xxx").Activate Columns("D:D").Select Selection.AutoFilter Windows("Where is everything commits .xls").Activate Windows("whereiseverything[1].xxx").Activate Selection.AutoFilter Field:=1, Criteria1:="7516113-905" \<--this Is one p/n Columns("J:J").Select Selection.Copy....................................
View Replies!
View Related
Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column
=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I) This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score. I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players. I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players. I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
View Replies!
View Related
Copy Column Of Data Removing Blank Cells
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F). I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9. Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.
View Replies!
View Related
Find Blank Cell In Column To Add Data
I need to write entries into an open spreadsheet with data input on a userform. i need to use the xlup facility to find the last used row in the spreadsheet, select the next line and then enter the data, but how to return to VBA the actual cell reference that has been selected after doing the xlup and down one row. I need to be able a total potential 31 rows of data from the userform.
View Replies!
View Related
Find Blank Cell Then Fill Column With Data
I have a userform that I am using to populate a column with data. I have the following code to find the next blank cell on the first row to enter the data from the first textbox in the userform ActiveSheet.Range("av1").End(xlToLeft).Offset(0, 1).Value = TextBox1 I was then going to populate the rest of the cells in the column by changing the range "A1" to "A2" and so on. The problem I have is that not all of the cells have a compulsory entry so when the end(xlToLeft) function may not always end in the same column and the data will be staggered. First Entry A B C D E 1X 2X 3X 4 5X Second Entry A B C D E 1XY 2XY 3XY 4Y 5XY What I want to do is find the first blank cell in the first row, as that will have a compulsory entry, and then fill the rest of the cells in the same column. So if the first blank cell is D1 i want to go down then D2,D3,D4 etc. I can do it going across the rows but cannot figure it out using columns.
View Replies!
View Related
Adding Column Minus Rows That Have Data In Another Column
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000. Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row... i'm not even sure if this makes sense lol... let me use example on SP-B there's a column M. in row 11, value = 3 in row 12, value = 5, in this same row column E is filled with whatever in row 13, value = 2, in this same row column E is filled with whatever in row 14, value = 6 in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
View Replies!
View Related
Count Data In Column X Based On Conditions In Column Y
I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2". In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.
View Replies!
View Related
Find Data In 1 Column Transfer To 2nd Column
I have a spreadsheet (XL2003) that calculates some price data based on a specific algorithm. The program then determines which price data meets a certain criteria and if met, will place the value in a column ( Column C in the example WS attached.) If criteria not met the cell is returned empty. Under certain settings, this column could have values in all rows (C9 to C27 in the example) but is usually just a specific consecutive list as in the example. I have to transfer the calculated values to another spreadsheet so I want just these values to be initially transferred to a separate column (Col. H in the example WS.) from which they can easily be transferred later to the other spreadsheet. I need to figure a way for a macro to check in col. C (ie C9) and if there is a value in the cell, to transfer it to the next available cell in Col H (Starting with H9). The macro would then look at the next cell down in Col C (C10) and do the same routine until the last cell is reached. The transferred data would then end up in consecutive cells in Col. H. I have shown in the example attached how I would want the data to look after the macro is run. I figured this is some type of loop but could not see any examples in the forum that would do what I need.
View Replies!
View Related
Make Multiple Charts That Will Graph The Data Of A Specific Name In Column
Is there a way to make multiple charts off of 1 big data sheet. Sounds easy but there is more. Sheet name is Field Column A is Name ( about 40 different names) Column B is Date (x axis primary and secondary) Column D is Oil (y primary axis) Column G is Water (y primary axis) Column E is Gas ( y secondary axis) Column M is MCFE (y secondary axis) There is about 2-3 thousand rows, updated monthly. I need to make a graph by each well name. 1) can I make multiple graphs that can just look at the "field" sheet and then breakout just the 1 well name that shows up on the chart sheet that I create I've tried pivot charts but you can't have secondary acess because it resets every time. Basically, make multiple charts that will graph the data of a specific name in column A
View Replies!
View Related
Check One Column, Get Value From A Second Column And Display Sum In 3rd Column
I am tying to total all the sales for a given customer. This is what I have at present Customer Sales CustA 1000 CustB 500 CustA 1250 CustC 750 Elsewhere in the spreadsheet I have the following Customer Total Sales CustA CustB CustC As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start. If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007
View Replies!
View Related
Add Cells In One Column Based On Specific Cells In Another Column
I would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.
View Replies!
View Related
|
TRACKER
