How to stretch a formula


#1

Hi, could you help me?
I need to stretch formulas on the sheet “NeedToStretch”. The formulas are on the range “A2:C2”. Just stretch as many times as many rows are on the sheet “Data”

Help, please :slight_smile:

Tmp.xlsx (9.0 KB)


#2

Hi,

There are a couple ways to solve this, but this what I would do…

First, you need to use the Select Range activity to select all cells where you want the formula. One way to find the last row you would need to probably use a Data Table from the sheet and use dt.Rows.Count to find the last row. Range would end up being something like this: “A2:C”+(dt.Rows.Count+1).ToString

Then, you need to perform the Filldown key combination which is “Ctrl+d”. You can do this using TypeInto “[d(ctrl)]d[u(ctrl)]”

Hope this is helpful.

Regards.


#3

Thanks!
It works :):slight_smile:


#4

But I found out that it works only if the excel file is active. If not, then UI Path doesn’t paste the values to cells, only select them :frowning:
As I understand, it is the feature of “Type Into”


#5

Yeah, the TypeInto needs it to be active to perform the Ctrl+d, but should do that automatically if it can find the selector.

If you want something more in the background then you’d probably need to use VBA or VBS and an Excel Object.

Alternatively in UiPath, you can use a ForEach row In datatable and use WriteCell to update the formula in each cell, but that’s more difficult I think.

Thanks.