I want to insert a new row that contains the formulas of a fixed row (1:1).
The inserted row is changeable and is determined by whichever is the current active cell.
Eg:
Active cell is something random like E16
I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
I am trying to use the code below to enter formulas into two different cells.
The first code "ActiveCell.FormulaR1C1 = "code" is running ok.
But the second one "ActiveCell.FormulaR1C1 = "=IF(IFERROR(IF(AND(1*LEFT(D2,2)>=61,1*LEFT(D2,2)<=65),VLOOKUP(AA2,'abc'!A:C,3,FALSE),""),"")="",D2,D2&AA2)" is getting error.
Sub Combine()
Dim wbk1 As Workbook, wbk2 As Workbook
fileStr = Application.GetOpenFilename()
[Code].....
I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows.
Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows.
example
If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7).
The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows.
2nd example
If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time.
Example:
Sheet 1 – is current jobs and sheet 2 is old jobs.
My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2.
I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
What I am trying to do is to give the user the ability to insert a photo and have it autosize into a range of cells--so far I can only get it to work with a single cell and a predefined file. Can this be refined so that rng references a group of cells and pic somehow lets the user input a file name or pick its name and location?
Sub test()
On Error Resume Next
Set pic = ActiveSheet.Pictures.Insert("C:
ange.gif")
On Error Goto 0
If Not pic Is Nothing Then 'Found it!'
Set rng = ActiveCell
With pic
.Height = rng.Height
.Width = rng.Width
.Left = rng.Left
.Top = rng.Top
End With
End If
End Sub
I have a "Match" formula in a cell that gives me the Row number of the Cell matching the criteria (lets say row 502) and the Column is always B. With VBA I want to make my ActiveCell the cell (B502) referred by the "MATCH" Formula.
View 5 Replies View RelatedI am desperately find a way to fill down formula from active cell, example given below,
AA
BB
CC
Total
QTY1
QTY2
City 1
5
2
3
10
12
15
[Code] .......
I need to fill down col Qty 1 & Qty 2. Number of Rows and Columns vary in my Work Sheet.
My code below, Ctrl+Enter not works.
HTML Code:
Cells.Find(What:="QTY1").Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]"
'Fill Down Active Column
Cells.Find(What:="QTY2").ActivateActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-3]"
'Fill Down Active Column
End Sub
Is there a formula in Excel that returns the active cell address (ie dynamically). Excel updates the activecell address in the Name Box dynamically as you make a selection but I cannot find a standard formula to access it. I know I can achieve this with code using the selection-change event but this action then disallows use of the Undo button - which I specifically want to avoid. Perhaps there is an add-in available?
View 9 Replies View RelatedI have a formula in cel B1 : =SUM(A2:A100) / A1
I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.
So I would need something like (literally):
=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.
I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.
What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.
In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
I 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.
For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?
The final formula I want is not that simple one. It is actually
=INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)).
I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)
I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.
Lets say I have cell (A1 for example) ='2013'!A39
Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on
I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39
Is this possible to do?
I am trying to create an active cell formula based of variables from given cells.
With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"
ActiveCell.Formula = ActiveCell.Value
But This does nothing.
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.
First of all I would like to insert 2 lines under line 6 in the active sheet.
I then need to drag the formulas found in line 6 down over the two new lines.
Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)
What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.
Next row gets C$3 and so on.
way to get a formula result to freeze or remain unchanged AFTER a certain time?
I tried using a macro which runs when the file is opened and does a crude 'copy/paste values' operation and replaces the Formula results with their values, but its is not very 'nice'!
I need a table to change a pattern of values but not in the past, but still to remember what that value was....!
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
It's for Excel 2003.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm‎
How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??
View 4 Replies View RelatedI am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies View RelatedI'm trying to insert the following formula into cell AA11. For some reason I keep getting a 1004 Runtime Error. I'm assuming there is some type of syntax error. I'm not really sure what I'm doing wrong.
View 6 Replies View RelatedI can't insert this formula to cell via VBA.
Code:
Range("AB38").Formula = "=TAN((RADIANS(VLOOKUP(G38,TABLE_24))))*(Z38-AA38)"
i am having problem with doing team, i would like to lock cell contain formulas but allow users insert row.
View 2 Replies View RelatedI am trying to insert an if statement in a cell. Part of the reference is in cell as4.
I want the if statement in cell as5
[Code] .......
I get a run-time error '1004':
Application-defines or object-defined error
The code works fine without the if statement, i.e.
[Code] .....
I try to put a formula to a certain cell using this code, but I get application defined or object defined error:
HTML Selection.Cells(1).Offset(0, 5).Formula = "=INDEX(range1;MATCH(""D""&C13;range2;0);MATCH(""S""&D13;range3;0))"
The weird thing is, that if I type the same formula itself to a cell, it works, so the problem shouldn't be with the formula:
HTML =INDEX(range1;MATCH("D"&C13;range2;0);MATCH("S"&D13;range3;0))
On the other hand I tried to replace the formula in my VBA code with simpler one, like this one and that worked aswell:
HTML Selection.Cells(1).Offset(0, 5).Formula = "=a1+a2"
I'm using the below to code to insert a formula in cell E1, what am i doing wrong here.
View 5 Replies View RelatedHow can a formula perform the equivalent of the keyboard alt enter in a cell?
ie Make =A1&A2 where A1 and A2 are cells containing text become
a1text
a2text
in the new cell.
I want to make changes to a cell's formula when a combobox option is clicked,
I can make the changes using sheet1.cells(x,y) = "=WHATEVER(bla,bla,false)"
The problem is that when I run the program and make a change in the combobox, false is written as 'false' in the cells formula, which drives it into not working and displays ####### as the cells result.