Inserting A Variable Formula With VBA?
Nov 23, 2012
I have a data set which is structured such that there are variable numbers of products (column A) from 1 - 48 and these repeat multiple times for each "Name" held in column B. I have attached an example of this which uses a data set with 7 entries reapeating 7 times.
I need excel to insert 2 formula for me multiple times which needs to varry according as follows
1. Calculate the average value of data in Column C - G for n cells starting at a specific cell (C2), I have inserted an example of this and highlighted it in yellow. This then needs to repeat down column C several times, the number of times this will repeat depends upon the number of different names held in column B. But I have this calculated already and stored in a cell im my main document.
2. Calculate the variance of each value in the x cells above from the average calculated in point 1 above. I have highlighted this also in yellow.
View 1 Replies
ADVERTISEMENT
Nov 23, 2012
I'm analysing some data sets with variable content, and need to insert blank rows every so often to provide space to do some analysis. The problem I have is that the position of the blank rows change according to the data set, I have been able to calculate how often the row needs to be inserted using VBA as part of my import routine and saved this value to a cell, but I cant figure out how to write the VBA which will insert rows using this value and stop at the end of the data set.
In the attached example I need to insert a row each time the data in column 2 (name) changes, this is the value I have calculated which in this example is 7. So in this attached file I need to insert a row in row 2, row 9 and so on etc. But this is what changes depending upon the "Product" held in colum 1.
View 1 Replies
View Related
Nov 28, 2011
I have a workbook with 2 worksheets. 1 Worksheet contains a data table and another table contains a form
Worksheet 1 (Data)
[A] [B] [C]
1) [Material #] [Batch #] [Destruct Form #]
2) 10 100A 1A
3) 10 101A
4) 20 200A
5) 30 300A 2A
6) 30 301A
Worksheet 2 (Form)
[A] [B]
1) [Destruct Form #] [3A]
2)
3) [Material #] [Batch #]
4) [10] [101A]
5) [20] [200A]
I am looking to create a macro which, when executed, will:
1) search sheet 1 column B for any instances where a "batch #" from sheet 2 column B appears
2) add in the corresponding "Destruct Form #" from Sheet 2 Cell B1 into sheet 1 column b
Therefore after the Macro is run, sheet 1 will look like this:
Worksheet 1 (Data)
[A] [B] [C]
1) [Material #] [Batch #] [Destruct Form #]
2) 10 100A 1A
3) 10 101A 3A
4) 20 200A 3A
5) 30 300A 2A
6) 30 301A
View 9 Replies
View Related
Feb 23, 2012
I'm trying to create a macro that will open automatically generated report outputs and manipulate the data.
The macro will run from it's own workbook (Report Macro.xls), sheets / cells within this workbook are available as 'helpers'.
So far what I have working ok is;
Via a browse dialogs in a userform in Report Macro.xls I select 5 different report outputs. I'm storing the full filename & path in Sheet Data, Cells B1:B6 (B1 being the filename & path to the macro file). In column C I have just the filename
The first file is opened and manipulated as required. The next thing I need to do with it is to open the second file (filename / path in B3) and put a Vlookup in cell 02 of the first file which will look for data in the second (the second file will only ever have a single sheet).
As a formula I'd be using
Code:
=VLOOKUP(A2,'Email 22FEB12.XLS'!$B$7:$C$13120,2,FALSE)
In VBA if I use;
Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Email 22FEB12.XLS'!R7C2:R13120C3,2,FALSE)"
It works great. However, the second filename will change and I need to take it from the macro file, sheet 'Data', cell 'C3'.
How can I use the contents of sheet Data, cell C3 in the VBA code above instead of the filename being hard-coded?
View 1 Replies
View Related
Oct 8, 2012
I have a userform with a numerous textboxes, which I have labeled with similar text and a unique number at the end (num1_txt, num2_txt, num3_txt, etc.). I am trying to create a for loop that sequentially references the numbers of the text boxes and pulls the user-entered value in the text boxes into an Excel document.
Here is the code I have so far...
VB:
i = 1
For i = 1 To 18
Dim cyltrack As String
cyltrack = "Me.cyltrack" & i & "_txt.Text"
If Me.cyltrack.Value = "" Then
Cells((cellcount + i), 13).Value = "*"
Else
Cells((cellcount + i), 13).Value = cyltrack
End If
Next i
I am running into problems in trying to convert the string variable, cyltrack, into hard code that I could insert into Me.cyltrack.Value.
View 4 Replies
View Related
Jan 25, 2010
Inserting Variable Number Of Rows and Populating Data
View 10 Replies
View Related
Feb 1, 2014
When inserting a row in lets say row 5, i want the formula to be kept for that row. (e5) Table doesnt seems to work. Any macro for this, and no button but automatically.?
View 2 Replies
View Related
Mar 26, 2013
Is there a quick way to copying formula's from one column to every other column? I am using about 1000 columns.
View 6 Replies
View Related
Nov 7, 2013
I'm trying to insert a formula into cell "A8" when cell "A1" changes. These formula gives me (into cell "A8") the next week day, depending on the value of cell "A1". If A1="FRIDAY", "A8" will be 08-11-2013.
The problem is that an error ocurred (Run-time error '1004'). If I put the formula directly into cell it works...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
ActiveWorkbook.Worksheets("Sheet1").Range("A8").Formula = "=TODAY()+8-WEEKDAY(TODAY()-MATCH(A1;{""MONDAY"";""TUESDAY"";""WEDNESDAY"";""THURSDAY"";""FRIDAY""};0))"
End If
End Sub
View 3 Replies
View Related
Mar 7, 2014
I have a simple time sheet that I want to insert weekly dates by the date I have. I don't know the formula for that. I've attached the time sheet. TimeSheet2.xls
View 10 Replies
View Related
May 4, 2014
I have some formula's that look at a range of rows
[Code] .....
The trouble is I insert rows to add new data to my sheet, this then changes the formula. $A$9 then becomes $A$10, I don't want this. I want the start of the formula to stay the same. How do I do this?
View 3 Replies
View Related
Aug 5, 2014
I'm trying to come up with a formula that allows me to insert the date of the previous trading day. Because for several reasons I can't create a column array of manually entered holidays, I'm trying to find a formula that would produce this. I've tried this, but excel doesn't like it.
=DATUM(JAHR(ARBEITSTAG(HEUTE();-1;{DATUM(Jahr(heute());01;01);DATUM(Jahr(HEUTE());12;25))})); MONAT(ARBEITSTAG(HEUTE();-1;{DATUM(Jahr(heute());01;01);DATUM(Jahr(HEUTE());12;25))})); TAG(ARBEITSTAG(HEUTE();-1);{DATUM(Jahr(heute());01;01);DATUM(Jahr(HEUTE());12;25))}))
View 6 Replies
View Related
Aug 4, 2008
Need "Copy formula when Inserting rows". For instance, A1=5, B1=10 but my formula set far behind in AE1=SUM(A1:B1). So,when I insert new row, I have to go to AE to drag the formula, is there any Macro or tips to simplify it?
View 14 Replies
View Related
Nov 27, 2009
I have this code for setting up a sheet to begin a calculation involving the solver add in. I am getting a "Run Time Error 13: Type Mismatch" on the line that inserts a formula into the cell. I have put the first offending line in bold in the code below. Is this a problem with syntax or is there something else that I have made a mistake on?
View 2 Replies
View Related
Jan 1, 2010
I would like to insert a formula in a column, the formula is in column D and must be something like "=C1-B1" for row 1, "=C2-B2" for row 2,...etc. I would like to do it without using a loop cycle in VBA...I have attached an example of the data I'm using..
View 3 Replies
View Related
Jul 31, 2012
I am trying to write a macro that will allow me to copy the text of a cell, and then insert text around that previously copied string. I am having a problem adding the copied text into my formula. Here is my code below:
Sub Bid()
'
Selection.Copy
ActiveCell.Value = "inventory levels are " & ActiveCell.PasteSpecial(xlPasteValues) & " and oversold by"
ActiveCell.Offset(1, 0).Select
End Sub
View 2 Replies
View Related
Mar 17, 2009
I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A.....
View 9 Replies
View Related
Aug 4, 2009
I have a spreadsheet with 10,000 rows. In column A dates are mentioned and in column B I'd like to calculate the specific Quarter via VBA code as I can't have formula's in my sheet.
My VBA code is:
Sub QuarterCalc()
Dim myCell As Range
Application. ScreenUpdating = False
For Each myCell In ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2))
myCell.Formula = "=INT((MONTH(RC[-1])-1)/3)+1"
myCell.Offset(0, 0) = myCell.Offset(0, 0).Value
Next
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Jul 18, 2006
some method by which a user can insert a row that will then copy any forumulas, not their resultant value, in the row above into the new row. The best would be for a user to be able to just use Insert > Row from the toolbar and then have a macro automatically run, but I am willing to use any possible solutions.
I know that there are other posts on this subject, but I can not figure out how to make the information in them work for me. I know that I need to go to View Code in order to insert the code, but I am not clear on what to do after that in order for the code to function, even how to exactly enter the code (should I enter into General or Worksheet, into Sheet1 or ThisWorkbook, etc..). I saw someone mention something about using now() somehow to get code to automatically run or using toolbar buttons, both of which I do not understand how to do.
View 9 Replies
View Related
Jan 12, 2014
I have a spreadsheet serving as a simple document register. Column A is a unique document reference number, which is a simple concatenation of some text which prefixes all of the document numbers, plus the date in "yyymmdd" format (from column B) and a 3-digit sequence number (from column C).
Column B has no formula - my macro just pastes a timestamp in there.
Column C is a sequence number field which has a formula comparing the "yyymmdd" part of the timestamp with the cell directly above it. If they are different then the sequence number becomes "001" - if they are the same then the sequence number is incremented by 1, so all docs created on the same day have an incremental sequence number.
Here's the problem. When I want to add a new document I need to copy the last row down to the next row (in preparation for the next document to be created by my system) and there are two ways I've been doing this - one which works and one which doesn't.
This works > Say my last row (with the hidden formulae) is row 13. I select A13:C13 plus the three blank cells directly below (A14:C14) and do a Ctrl+D to copy them down. All is well.
But the following doesn't work - why? > I select the entire row 13, copy it, then reinsert it at row 13 so the formulae in row 13 get shifted down to row 14. What goes wrong here is that my formula in column C which WAS comparing the date in B13 to the one in B12 NOW compares B13 to B11
I've attached an example spreadsheet to try and demonstrate the problem. Sheet 1 is the original sheet. Sheet 2 is the "copying down" method which works, and Sheet 3 shows what happens when you copy the entire row 13 and "Insert copied cells".
excel-insertrow-formula-problem.xls
View 9 Replies
View Related
Feb 6, 2014
I am having trouble looping through a range and inserting a formula where a condition is met.
My range is "h9:i"
My current code inserts a formula in column i when there is a value in the adjacent row in column h:
[Code] .....
However, because there are so many rows (12,000+) it is taking a considerable length of time to run.
Some of the cells in column i will already contain the relevant formula, so to speed this up I want to insert the formula in column i only when the cell value (in column i) is not null, as opposed to inserting it for all cells within the range.
I know how to loop through the range, but the problem is the 'H9' cell reference in the formula in the code above will need to change depending on what row it is being inserted into- so if it is going into "i31" this will need to be "h31".
View 7 Replies
View Related
Jul 10, 2009
I have a function Sum('1st Qtr:4th Qty'!AW1) in row 1 and dragged down 129 rows so the last reads Sum('1st Qtr:4th Qty'!AW129). If I insert a row the cell reference does not increment automatically below the insert location. How do I get it to do so. The insertion occurs when I run a macro.
View 3 Replies
View Related
Jan 10, 2012
I have a spreadsheet that I can modify. It currently has a three product column but I need to insert 2 or 3 more columns to make 5 plus the total at the end. It also have a summary sheet. I insert the 2 columns and somehow got the formulas flowing. However when I hit the summary page its not showing results for those two new columns in the total. I am lost. I wish I could post the sheet. Its a multiple product break-even analysis exercise.
View 1 Replies
View Related
Jan 2, 2013
What will show in column B will change each day as a I am creating a ordering picking list and want to show a pic in column A of the item to pick the item. Is there an easy way of doing this.
I have been messing around with the following
=showpic("C:DesktopItems46004978Thumb.jpg")
And thought I may be able to show this as by doing the following in the desired cells
=IF(ISBLANK(B1),"",INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0)))
On sheet1 I have all the model numbers next to
=showpic("C:DesktopItems46004978Thumb.jpg")
And the index formula will grab the correct showpic string depending on stock code that is showing in various column B cells but this isn't working.....
View 9 Replies
View Related
Aug 16, 2013
I have an Excel file that contains several worksheets. I would like to add a forumla to the next available cell. For example, if coulmn F contains the set of numbers to be sum the formula should be placed beneath the last number in column F. Each worksheet has a different length, the worksheet name will vary, and the number of worksheets will vary.
I know how to accomplish this task using the code below when the sheet name is known. Since it is not known I am unsure of the code.
Sheets("RawAmt").Select
NxtRw = Cells(Rows.Count, "C").End(xlUp).Row + 1
With Cells(NxtRw, "C")
.Formula = "=SUM(C1:C" & NxtRw - 1 & ")"
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
View 7 Replies
View Related
Feb 9, 2014
My VBA is limited so I'm drawing a blank,
I am trying to create a macro that inserts a formula based on data in column A (looking up in same row) and the header (Stored in Row 2 of the column - This is a fixed cell)
Formula I can currently copy and paste in there is
=VLOOKUP($A1,'Current IW15'!$A$1:$L$1962,MATCH(Workpack!J$2,'Current IW15'!$A$1:$L$1,0),FALSE)
(I know it's bad form to match in the vlooukup, however it works in this form...)
So far I have
Private Sub CommandButton1_Click()
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,'Current IW15'!$A$1:$L$1962,MATCH(Workpack!R2C,'Current IW15'!A$1:$L$1,0),FALSE)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
But it doesn't work
View 2 Replies
View Related
Aug 27, 2008
inserting a formula in PIVOT table field....
View 9 Replies
View Related
Aug 21, 2014
I manage to find a macro online, which aims to insert a desired formula into desired cell range. However, when I execute the macro, i get a "run-time error, 1004".
View 1 Replies
View Related
Jul 25, 2014
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
View 3 Replies
View Related
Feb 13, 2008
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
View 3 Replies
View Related
