How to auto select the column instead of typing Column Number for a formula?

Help Studio
1

Hi i needed some help where in my book 4 i would like to auto write a formula of a multiple of the cell column

Book4.xlsx (9.2 KB)
test copy paste and auto create number.xaml (6.9 KB)

instead of being static let say my excel row is dynamic how can i output this
image
where i dont do such thing like A3*B3 etc ?

2

Hi @xxGoRpa ,

One of the methods would be to Iterate over the Datatable that is the Excel file and Update the Column Value Accordingly.

You can follow the below Steps :

  1. Read the Excel file using Workbook Read Range Activity. Assign an Output Variable named DT.

  2. Next you can use an For Each Row In Datatable Activity, provide the Input Datatable as DT.
    Inside the For Each Row use an Assign Activity with the following :
    row("Total") = CDbl(row("Quality").ToString)*CDbl(row("Price").ToString)

  3. Outside For Each Row, Use Workbook Write Range Activity and use the DT value as the Output Datatable with Add Headers Property Checked.

If that is not the Expected Solution that you required, Request to share more information on how dynamically you want to access the values.

2 Likes
3

Hi @xxGoRpa ,

If the column you want to insert formula is constant then you can on the first cell say C2 using write cell activity write the formula like “=A2*B2” then you can use Auto fill activity and in the range of that you can pass something like say range - C2:C+(Dt1.Row.Count+1).tostring.
This will drag the formula till the required limit

Cheers

1 Like
4

One way you can do it is using “Auto Fill Range” activity to add formula to a range. You can refer to the screenshot below. My “Write Cell” activity is a bit complex as I used Chr(34) to include comma in formula.

image
image594×554 17.9 KB