fill row with formula

Hello,

I have a macro that add rows and I fill them with data.
I want to insert to onw of the rows a formula so it will be available in the added rows.

the formula is:

=IF(E9="שבת","00:00",IF(K9-J9<0,"00:00",K9-J9))

any idea?

Thanks

just assign the desired cell with the formula

for example if the column is 20 and the row is newrow

cell(newrow,20)="=IF(E9="שבת","00:00",IF(K9-J9<0,"00:00",K9-J9))"

Hey,
Thanks for your replay.

I have 2 problems with your example:
1. E9 is a row which I copied the formula from but I use this formula in every row in that column. so, the new row will be E_ (the number of the row which I dont know)
2. its returns me an error : "syntax error"

I will explain again what I want to do.
I'm calculating the sum of the values in each row in that column. I used this formula because I have raws without value so I wanted to insert 0 so it wont return an error. so the point is that I want to enter the value that the user will enter in the new row that have been added by the macro to the sum calculation.

Yes I see the problem,

One way to overcome that is using the indirect reference. could you set up a sample and I will set up the appropiate formula to populate the cell correctly.

ActiveCell.FormulaR1C1 = "=IF(RC[-15]=""???"",""00:00"",IF(RC[-9]-RC[-10]<0,""00:00"",RC[-9]-RC[-10]))"

How can I upload a file here?

What does R1C1 means? and wy RC[-15]...?

this is a different way of entering formulae.

did you try it?
R1C1 format means that you Row 1 Column 1.
RC[-15]. If your formula were in row 1. column 20 then this would be looking at the same row but 15 columns to the left.

get your macro to put this formul;a into your spreadsheet. it will work

Hello,
Thanks for your reply.
I need the formula to be at each row at the same column so I need it to look for example at the same column but 10 rows up.
How can I do that?

attach the dummy file.

Hello,

I've attached a sample file with macro.

Thanks for your help