I need a macro that will insert a column next to column B, and insert formulas into the new respective cells. forumula needs to be in the macro, it won't be located somewhere else on the sheet (for simplicity's sake, let's just say the formula is =A1+B1, then A2+B2 in the next row, etc). Also, it needs to stop inserting formulas when there's no more data in column B.
View 1 RepliesI have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.
For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?
I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.
The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.
I am using the following ....
I have a table where random records that are automatically inserted when you press the command button 1, I managed to set all columns exept the last column (column O) because I don't know what shall i do to enter the following formula: =
(L7 -SOMA.SE (D7: N7, D7; K7: K7)) + SOMA.SE (D7: N7, D7, N7, N7)
This formula should be inserted in the button code in order to automatically calculate the value of O7 after that when i press the button he must calculate O8 and so on.
It is important to gradually increase the formula references cells +1, so it would be for the cell:
O7 -> = (L7-SOMA.SE (D7: N7, D7; K7: K7)) + SOMA.SE (D7: N7, D7, N7, N7)
cell O8 -> = (L8-(SOMA.SE (D7: N8, D8, K7, K8)) + SOMA.SE (D7: N8, D8, N7, N8))
cell O9 -> = (L9-(SOMA.SE (D7: N9, D9, K7, K9)) + SOMA.SE (D7: N9, D9, N7, N9))
10 cell -> = (L10-(SOMA.SE (D7: N10, D10, K7: K10)) + SOMA.SE (D7: N10, D10, N7, N10))
and so on
already have my button the following code:
Private Sub CommandButton1_Click ()
Range ("c65536"). End (xlUp). Offset (1, 0). Select
ActiveCell = Now
[Code] ........
I want to create a macro which inserts a new colum after the last colum with data.
View 3 Replies View RelatedI have a spreadsheet where I need to insert a column then a formula that performs a calculation on the previous column, all the ranges change; so far I ahve managed to insert the column to the right of the one I want to perform the calculation on and insert the formula in the first line, but i'm having a problem copying this dowm to the end of the data range.
View 3 Replies View RelatedI am making a deposit sheet schedule, which has 10-15 batches of deposits that are entered one after the other, vertically. Each deposit entry can be allocated to many different expense accounts, so a deposit will be 500 with 400 to rent, 50 utitilies, etc. So I need multiple summation rows
So, I want to add a sum row beneath each set of deposits.
My problem is that each deposit is a different amount of rows long.
I was thinking of doing something like this:
'totalRow() is each row where the summation formulas will go totalRow(0) is not an actual sum row, but tells me where the first row of data lies.
For Each cell In Range("H" & (totalRow(x - 1) + 1) & "", "J" & (totalRow(x - 1) + 1) & ":N" & (totalRow(x - 1) + 1) & "", "P" & (totalRow(x - 1) + 1) & ":AB" & (totalRow(x - 1) + 1) & "")
cell.Formula = "=sum(Range(""A"" & (totalRow(x - 1) + 1) & "":A"" & (totalRow(x) - 1)"
Next
I don't know how to reference the ranges I want to sum, because I don't know how to increment the reference to the column (i.e. changing A to B to F to G, etc.) with the Range().Formula construct.
I was thinking of using R1C1 somehow but I'm not as familiar and I don't think you can use r1c1 & Range() at the same time.
I would like to automatically insert a row at each change in value in one column. See the attached sample. When the value in column C changes, I would like a row inserted. Preferrably without using any macros because they are way beyond my skill level and understanding
View 2 Replies View RelatedI need Macro to insert a specified number of rows in one worksheet and the same number but columns in another worksheet.
The rows are inserting fine but I can't get the columns to work. Here is what I have so far:
Sub InsertRows_Columns()
Dim iRows As Long, rngStart As Range
On Error GoTo Canceled
iRows = InputBox("How many unit rows would you like to insert?", "Number of Rows", 5)
[Code] ....
Canceled:
End Sub
I have 900+ excel files, with different number of columns. In all the files I want to cut the last three columns and insert them after column "L".
Can I also have the format of the three inserted columns same as Column "L".
I once heard that in order for a macro to insert a formula you must double all the quotations. Anyway, i did so in the following macro but i got an error message.
View 3 Replies View RelatedI have code for auto insert row, when i m getting to new row column Q formula not working
remove the auto generate serial number in column column A, only i want to insert auto with column Q formula to work.
find enclosed a worksheet for your ref.
Please see the below auto insert code;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim O As Range
Set O = Range("O:O")
If Intersect(Target, O) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Rows(Target.Row + 2).Insert
Application.EnableEvents = True
End Sub
I have this formula that I'm trying to insert into cell AE2, then copy down the entire column. When I execute it, none of the cells are populated.
Range("AE2").Select
ActiveCell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$2/365)/P2)"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE3495")
Range("AE2:AE3495").Select
I would like a macro to order the columns as per the hardcoded list. If the column is not found than add the column at the position as per the hardcoded list. Below macro does the first part of re-ordering however if the column is not found how do I add an empty column:
Sub Reorder_Columns()
Dim ColOrder As Variant, i As Integer, Found As Range
ColOrder = Array("LogicalFileName", "LogicalFilePath", "UploadedDate", "UploadedBy",
[Code].....
I need a macro that will insert this formula: =Trim(IF(B3="","",MID(B3&", "&B3,FIND(" ",B3)+1,LEN(B3)+1))) into column C starting on row three through the end of the spreadsheet information.
I typed this incorrectly when I first posted. I need it to fill down into only column C not the end of the spreadsheet.
My macro inserts the formulas correctly up until the last loop where I get an Application Defined or Object Defined error on the .FormulaR1C1 line. The msgbox returns the correct values.
iCtr = 0 'data starts on 1
For c = 6 To 14 'columns F to N
iCtr = iCtr + 1
MsgBox ("Column " & c & " Index " & iCtr)
With . Cells(6, c)
.HorizontalAlignment = xlCenter
.NumberFormat = "#,##0;;"
.FormulaR1C1 = "=IF(ISBLANK(INDEX(MyNamedRange," & iCtr & ",1)),"""",'" & Replace(MainPage.Name, "'", "''") & "'!R" & NextRow & "C6)"
End With
Next
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 12 Replies View RelatedPls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.
View 7 Replies View RelatedI have a folder for each month, and in the folder there are worksheets for each day of the month. eg for January folder, it will contain 31 worksheets. They all have the same column headings. I want to know if there is any way i can use a macro to automatically insert a formula in each worksheet, using loop?
View 2 Replies View RelatedI have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.
I have managed to insert blank lines on change in values in column B, but am not sure about the best approach for inserting the SUM formula or the IF statement in columns G and J respectively.
I have recorded a macro code for which is as follows. This Macro goes into a worksheet and gives percentile value for a range of data. But this is becoming cubersome as this sheet is 65531 rows and can span multiple spread sheets. Can this be put in a loop of some sort to go through the whole worksheet? ....
View 9 Replies View RelatedI am using a For Next statement that doesn't return the results for all the rows. The statement is as follows:
For Row = 1 To 100
If ActiveCell.Value = "CHANGE" Then
ActiveCell. Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(""0000""&RC[-1],20)"
ActiveCell.Offset(1, 0).Range("A1").Select
ElseIf ActiveCell.Value <> "CHANGE" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else: Range("A1").Select
Exit For
End If
Next
Range("A1").Select
I hope I did that according to the rules. It only returns the result in the first cell that does have a value of "CHANGE". It seem to be going through the entire range of cells, but I'm not getting any results.
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
And this code for deleting the column:
VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"
[Code] ........
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.
Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.
Just Column E - I need an autosum in the "empty row 1's)
23.00
15.00
6.00
3.00
empty row 1
empty row 2
6.00
3.00
empty row 1
empty row 2
6.00
25.00
3.25
6.32
I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
I would like to create a change event macro that will update multiple formulas that pull data from various tabs on a file. The tabs are named with a single date. So ideally when a certain master cell (Allocation!H2) is updated to the current date the macro will be prompted to update the range that contains formulas and update accordingly...so its and index match fomula based that has a variable being the tab name which is a date. I have a range of dates in cells F4:AB4 and want the formula to go into F5:AB5, I can then fill that down to whatever row I want...that bit I can handle.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Worksheets("Allocation").Range("H2")
[Code]......
I have a formula that works fine in the cell of an Excel spreadsheet but I'm struggling to translate it into VBA (your help please).
The cell formula is:
=If(B2="", "", B2 & " (version: " & F2 & ")")
I want to iterate through all rows in my spreadsheet (about 2000) incrementing the relevant row numbers in the formula @ each pass - eg changing B2 -> B3 -> B4 etc and F2 -> F3 -> F4 etc where column 'B' contains the name of the product and column 'F' contains the version number, resulting in "Product Name (version: 123)" per row.
The closest I've got to this is:
Sub LookupNameInColumnA()
Range("A2").Select
Dim i As Integer
For i = 1 To Selection. CurrentRegion.Rows.Count - 1
ActiveCell.Formula = "=IF(B2="""", """", B2 & "" Version: 999"")" ' problem line?
ActiveCell.Offset(1, 0).Select
Next i
End Sub
This works OK'ish but I want each line to reflect the different data per row. I'm struggling to increment row 'B' and row 'F' in the formula, having tried "B & i + 1" and "F & i + 1" but am getting confused with quotes and concatenating strings within formulas.
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
View 4 Replies View RelatedHello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.
I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.
Public Sub SubColumn(CritStr As String) ...