Question

Hi Team, Can anyone help me how can i drag the excel formula and update the rest of the cells.


Hi Team, Can anyone help me how can i drag the excel formula and update the rest of the cells.

4 replies

Userlevel 6
Badge +15

Hi @OM Mishra​ ,

 

Please check out the below link

Set Cell Formula in Excel AutomationAnywhere A2019 ...

Badge +4

Hi @Tamil Arasu10 it took quite long time to have the formula for the rest of the column if rows if more than 2k. Is there any better solution?

Userlevel 7
Badge +13

Hi @usernsj,

Using Set Cell Formula within a loop and increment the Row number will be the suitable. But since your data is huge, I would recommend to you the Database package through which, the execution will be faster.

Badge +1

On large data sets I have two options for you. Hopefully this helps and let me know if you have any follow up questions.

  1. Using Keystrokes (Simple to set up, but only works if the data in the column next to your formula goes all the way to the bottom and if your formula column is completely empty when you start.)
    1. Set Cell Formula: Put formula into the first cell.
    2. Simulate Keystrokes: While on the cell that contains the formula you can press [CTRL DOWN]c[CTRL UP][LEFT-ARROW][CTRL DOWN][DOWN-ARROW][CTRL UP][RIGHT-ARROW][CTRL DOWN][UP-ARROW][CTRL UP][CTRL DOWN]v[CTRL UP]
  2. Using Get Number of Row (much more reliable, but requires a variable and more steps to set up)
    1. Put the action “Get Number of Rows” into your code.
      1. In the action option select index or name of the worksheet you are working with
      2. Select non-empty rows or total rows with data. 
      3. Put in your excel session name.
      4. create a variable to hold the total row count.
    2. Set Cell Formula: Put formula into the first cell.
    3. Simulate Keystrokes: Copy the formula [CTRL DOWN]c[CTRL UP]
    4. Excel advanced: Select cells/rows/columns.
      1. use the option of cells.
      2. use the option cell range and put in this range.
        1. B3:B$ExcelRowsCount.Number:toString$
    5. Simulate Keystrokes: Paste the formula in the selected cells.
      1.  [CTRL DOWN]v[CTRL UP]

Reply