Row Insertion and Formula updating

[farhan_babu]

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.

 

[venkat1926]

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]

farhan_babu
Welcome to the Board !!!!!

do you mean this should be done automatically ?

kind regards,
Erik

 

[farhan_babu]

I want to do so through VBA.Hope u havel understand my porblem

 

[erik.van.geit]

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]

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]

still not sure what you would like to do
assuming the specific cell is B5
two methods, different results

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]

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]

one of these ?

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]

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.