Row Insertion and Formula updating

farhan_babu

New Member
Joined
Nov 19, 2005
Messages
13
Hi,

I m new in this forum,My problem is that I have cloumn in which a seriesof cells has same formula now I want to do such a thing if a row is inserted by a user the new row has the same formula which is in above cell.I hope u have understood my problem and guide me.

Thanks in advance.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
jut copy the previous cell to the new cell. only ihope formula take care of absolute and relatie references needed.

or even put the cursor at the bottom right of the previous cell(it waill + sign) and drag down
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
farhan_babu
Welcome to the Board !!!!! :)

do you mean this should be done automatically ?

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

then allow only to input rows using a button which will be the safest way
do you want to insert entire rows or just ranges or single cells ?
 

farhan_babu

New Member
Joined
Nov 19, 2005
Messages
13
Hi,

Thanks for quick reply
I want to update specific cell which is not having formula(the new row,s cell) in the best way.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

still not sure what you would like to do
assuming the specific cell is B5
two methods, different results
Code:
Sub insert_cell1()
Dim cell As Range
Set cell = Range("B5")

'ActiveSheet.Unprotect "password"
cell.Insert
cell.Offset(-1, 0).Formula = cell.Formula
'ActiveSheet.Protect "password"
End Sub

Sub insert_cell2()
Dim cell As Range
Dim CF As String    'cell formula
Set cell = Range("B5")
CF = cell.Formula
'ActiveSheet.Unprotect "password"
cell.Insert
cell.Offset(-1, 0).Formula = CF
'ActiveSheet.Protect "password"
End Sub

How to create a button and assign a macro to it:

If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen; drag it down to the bottom of your screen to anchor it.

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.


kind regards,
Erik
 

farhan_babu

New Member
Joined
Nov 19, 2005
Messages
13
HI,

I want this action on row insertion like this
if activecell.entirerow.insert and activecell.offset(-1).hasformula then activecell.formula = activecell.offset(-1).formula
end if
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
one of these ?
Code:
Sub insert_cell1()
'ActiveSheet.Unprotect "password"
ActiveCell.EntireRow.Insert
    If ActiveCell.Offset(-1).HasFormula Then
    ActiveCell.Formula = ActiveCell.Offset(-1).Formula
    End If
'ActiveSheet.Protect "password"
End Sub

Sub insert_cell2()
'ActiveSheet.Unprotect "password"
ActiveCell.EntireRow.Insert
    If ActiveCell.Offset(-1).HasFormula Then
    ActiveCell.Offset(-1).Copy
    ActiveCell.PasteSpecial (xlPasteFormulas)
    Application.CutCopyMode = False
    End If
'ActiveSheet.Protect "password"
End Sub
 

farhan_babu

New Member
Joined
Nov 19, 2005
Messages
13
sir,

Unfortunately I can,t explain it properly. I want to say if a user insert a row then update formula automatically(some type of auto code) not need to run the procedure.

Hope I have explained it.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,536
Messages
5,572,774
Members
412,482
Latest member
arooshrana2
Top