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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,806
Messages
5,833,778
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top