Hi Rajath.Gopal
As I see it, your only option is to go to the next empty cell and paste the formula to ALL of the cells below this next empty cell. Of course this is only a solution if there are no rows with information below where you are pasting the formula. If yes, then I have a suggestion for a solution. But in order to do this, you need to send Global Send Keys and Key events. Therefore, you have to create an Excel BO that opens C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE (or whatever version of excel you have) and launches and attaches to your excel workbook with this BO.
1) Use MS Excel VBO action ""Attach To Workbook"" to get the handle of the Excel workbook. Use this handle as input to MS Excel VBO action ""Go To Next Empty Cell Down"" to get the cell-reference of the next empty cell/line on the worksheet in your workbook.
2) Create an Excel BO and create a page that has 1 navigate stage with 3 actions:
a) Use the action ""Global Send Key Event"" with the application root element, and input Text = ""{CTRL}"" to send Shift + Ctrl + down arrow, this selects the entire rows in the worksheet from the cell-reference and down. Make sure that you are in the cell-reference that you found in 1), when using the navigate stage. A good idea could be to pass this as input to and use it in your Excel BO, if somehow you should end up in another cell, when switching between the 2 BO's.
b) Use the action ""Global Send Keys"" with the application root element, and input your formula in input Text e.g ""=3*3"".
c) Use the action ""Global Send Key Event"" with the application root element, and input Text = ""{CTRL}"" to send Ctrl + Enter, so that the formula is copied to the cells from cell-reference and down.
Set ""Pause After Each Step to 0,1.
Then you simply re-attach to the MS Excel VBO again and carry on with your business.
Good luck, let me know if you have any questions.
BR,
Mustafa
