Sub formulainvariablerows()
lr = Sheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox lr
Sheets("sheet2").Cells(2, "a").Resize(lr).Formula = "=a1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shikha" wrote in message
...
Hi All,
I have 2 worksheets, for which I am comparing data and showing result in
3rd
worksheet, in same workbook. I am able to do this for fixed number of rows
by
dragging formula in 'result' sheet to no of rows present in Sheet1 and
Sheet2. However, number of rows in sheet1 and sheet2 is not fixed and I
want
that formula in 'result' sheet automatically gets copied for no. of rows
in
sheet1, by a click of a button. Is there any way to do this?

Regards,