Whenever i insert Row on Top, Second Row should have Fix Formula.

[sajjjid]

Need formula at C10 & E10, to come automatically whenever I insert a ROW on Top.
Every month i will insert a row. from January to December. Thanks.

 

[Hui]

If it only happens once a month, simply copy the formulas below up

 

[bosco_yip]

Maybe....

C9, copy down :

=IFERROR((OFFSET(B9,-1,0)-OFFSET(B9,0,0))*1.5,"")

Regards

 

[sajjjid]

If it only happens once a month, simply copy the formulas below up

Yes, ur right, i am doing same thing. but still i thought there should be formula or macro for this. e.g. if b9=January-december then c10=sum(2+2.)
Thanks.

 

[sajjjid]

Maybe....

C9, copy down :

=IFERROR((OFFSET(B9,-1,0)-OFFSET(B9,0,0))*1.5,"")

Regards

Not Working. It shows blank.

 

[Belleke]

Does something like this helps you further?
See attached

 

[sajjjid]

Does something like this helps you further?
See attached

Yes This work very nicely.
Now how i will take this insert button to my original file.? Thanks.

 

[Belleke]

Hi, glad that helped.
How to explain ? I am working with a Dutch excel version. explaining is not that easy.
1. this is the code

Sub belleke()
ActiveCell.EntireRow.Insert
Range("A" & ActiveCell.Row).FormulaR1C1 = "=EDATE(R[1]C,1)"
Range("A" & ActiveCell.Row).NumberFormat = "mmmm"
Range("C" & ActiveCell.Row).Offset(1).FormulaR1C1 = "=(R[-1]C[-1]-RC[-1])*1.5"
Range("E" & ActiveCell.Row).Offset(1).FormulaR1C1 = "=(R[-1]C[-1]-RC[-1])*1.5"
End Sub

You can put that under any shape you create or under a button from the developers ribbon tab.
or an active X controlbutton from the developers ribbon tab. (what you prefer.)
2. See also the formulas from A12 up until april, copy these when you want to start with may from then it goes automaticly if you use the code.
Hope it is clear.

 

[sajjjid]

Hi, glad that helped.
How to explain ? I am working with a Dutch excel version. explaining is not that easy.
1. this is the code

Sub belleke()
ActiveCell.EntireRow.Insert
Range("A" & ActiveCell.Row).FormulaR1C1 = "=EDATE(R[1]C,1)"
Range("A" & ActiveCell.Row).NumberFormat = "mmmm"
Range("C" & ActiveCell.Row).Offset(1).FormulaR1C1 = "=(R[-1]C[-1]-RC[-1])*1.5"
Range("E" & ActiveCell.Row).Offset(1).FormulaR1C1 = "=(R[-1]C[-1]-RC[-1])*1.5"
End Sub

You can put that under any shape you create or under a button from the developers ribbon tab.
or an active X controlbutton from the developers ribbon tab. (what you prefer.)
2. See also the formulas from A12 up until april, copy these when you want to start with may from then it goes automaticly if you use the code.
Hope it is clear.

No, I did not succeed in copying that code.

 

[sajjjid]

Plus that should work upto "S"

 

[Belleke]

Can you post an example of the real file with some dummy numbers, names in it?

 

[sajjjid]

Can you post an example of the real file with some dummy numbers, names in it?

Image Attached.

 

[sajjjid]

Thanks very much BELLEKE
I made shortcut key as ctrl-r now it is working and i added upto "S"
Bye.

 

[Belleke]

Thanks for the feed back

 

[vletm]

sajjjid
Test this version...
1st You have to select 'Month'-cell
2nd press [Insert Row]

note: Would months would be filled with dates?

 

[sajjjid]

sajjjid
Test this version...
1st You have to select 'Month'-cell
2nd press [Insert Row]

note: Would months would be filled with dates?

This also works fine. Thanks.

 

[Somendra Misra]

Hi,

Try below formula in Jan month row and copy when ever you insert row.

=(OFFSET($B10,-1,0,1,1)-B10)*1.5

Regards,

 

[sajjjid]

Hi,

Try below formula in Jan month row and copy when ever you insert row.

=(OFFSET($B10,-1,0,1,1)-B10)*1.5

Regards,

No, this not working.