Insert Formula After Every 2 Columns
Dec 5, 2007In my spreadsheets, I want to insert a formula in the existing columns of data. This is to be done after every 2 columns. There are 25 rows as well from 5 to 30.
View 9 RepliesIn my spreadsheets, I want to insert a formula in the existing columns of data. This is to be done after every 2 columns. There are 25 rows as well from 5 to 30.
View 9 RepliesI have to work on sheets with variable columns.
So for eg Col A Col B Col C Col D are fixed and then there are variable no of columns - Col E Col F and so on....
I have to insert columns after the last colum ie in Col F in this case.
However, the new column (lets call it Col G) will give a value with formula that connects Col E value with Col B (fixed)value. Again Col H (another new col) will also give a value with formula connecting Col H with Col B(fixed).
Easy to do in normal scenario but when I record macro in excel ( i cannot write VB), the variable columns make it difficult.
I have two variables - the number of stages, and the number of people.
The stages will start in say C22 through G22 (if there were 5 stages). The number of stages will vary upon a user-inputted number in A1.
The names of people will start inB29, so I'd like to insert the simple formula (=Max(C23:C28)) across cells C29-G29 (again with my example of 5 stages), with relative references to the columns, of course.
Ditto for the next name in B30, etc, so that I get the max value for Person#1 in Stage 1, 2, 3...Person#2 in Stage 1,2,3...
Inserting the formula over the dynamic ranges.
I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?
View 4 Replies View Related I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies View RelatedI have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
I am unable to insert columns on any worksheets. Even a blank worksheet. The insert column function remains disabled. Work sheets are not protected. What can I do to activate this function?
View 10 Replies View RelatedI want to compare Columns A and B and Insert value in C.
So if Column A is null, then place value of column B in C, otherwise place Value of A in C.
Worksheet full at Column IV how can I insert more
View 1 Replies View RelatedI have this excel file which has data in it. However, this data will come in everyday. Eg, A1 to A10 is QWE, A11 to A20 is RTY, A21 to 30 is UIO. But as I said earlier new data will come in everyday. For eg, it will become A1 to A15 is QWE, A16 to A30 is RTY and so and so forth.
I need to insert 2 rows after QWE, RTY, UIO. But as data will come in everyday, I cant standardise my columns to insert the 2 rows.
somehow i managed to do this at first and then forgot to save my personal work book and cant remember how to do it.
What it is, is if cell A1 has a certain phrase in it ie Customer Reference then i want to insert four columns before it so it ends up in cell E1.
The other thing which i never managed to do is. I have 5 workbooks. master, book 1-4. I want to past all info in columns A:T from Book 1-4 into Master in the next available row.
I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns).
Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.
I have many excel sheets with many columns. I want to insert new columns but after every 2 columns, starting from column M.
View 9 Replies View RelatedMy question is if there is any way I can make the "X" number typed in a cell be the X number of columns inserted after/before a cell, B12 (for example).
View 2 Replies View RelatedI have attached an example of what I need the macro for. I really need it to look at the columns with the names (Girth Weld, Weld) and the Joint length column to make its moves. Mainly the joint length. I think that would get me on track and give me some room to explore and learn what else I need to do. I love to try and figure things out but this is killing me!
[URL]
VB:
Sub LineUp() 'assumes data in order and concates unique
Dim i1 As Long, i2 As Long, n1 As Long, n2 As Long
Const StartRow As Long = 5
[Code].....
What is the easiest way to stop users inserting new columns or rows into a workbook? Is it a case of protecting each sheet individually?
View 6 Replies View RelatedI am unable to insert sheet rows or columns in a microsoft office spread sheet in a specific file. Other files are Ok.
View 14 Replies View RelatedI have a macro that inserts a blank column for every sheet in the workbook. The problem i have is that i only want a limited number of columns inserted. I only want a column to be inserted if the sheet tab has the name "detail" in it. I have tried for hours to get this to work, but i keep getting errors. Here is the code i am using.
View 4 Replies View RelatedI would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.
Or is there perhaps a faster way than using a macro?
This is for my fantasy football league, so perhaps being familiar with fantasy football will be of help. It's somewhat complicated, so I'm just going to explain what I would like to do: Upon opening the Excel file, there will be a few questions. The first will ask what type of draft, with just two choices, A or B. Below that, it will have inputs asking how many teams, how many players per team, and, if option B was chosen, how many dollars per team. After answering the questions, there should be a button that will "generate" what I'm about to describe.
Choosing option A or B will result in two completely different sheets: -Option A will have X columns for the number of teams entered, and Y rows for the number of players per team. If possible, something will pop up allowing the user to input each team name.
-Option B will be a bit more complicated. I have a template of what one column will be for each team, and would need to have that repeated for the number of teams entered. Additionally, the number of players per team and dollars per team will need to be placed into an existing formula that will perform certain calculations. And, if possible, the number of players per team will alter the column template if the number is over a pre-defined number. And, like the previous option, it would present a pop up allowing the user to input each team name.
I apologize for the length of this question, and while it is too extensive to ask someone here to do, I would like to know what I can expect if I try to achieve this. Will I need to know programming code? Can you point me to some articles that will help me in terms of turning an inputted number into a spreadsheet with that number of columns after hitting a "submit" button?
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 RelatedWriting a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code:
Sub renameColumns()
With Sheets("byPosition")
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
.Range("E1").Value = "Exemption"
.Cells.EntireColumn.AutoFit
[Code] .......
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 '
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: ....
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
View 3 Replies View RelatedI have 2 workbooks each with last name columns. One book has a column of a few hundred names and the other has a column of over a thousand. I need to compare the last names and pull out the employeeID (which is in a seperate column) to a specific column for the ones that match.
View 2 Replies View RelatedI have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................