How to do Auto Fill Range Activity in Excel Automation?

Help
, ,
1

Hi,
I’m trying to automated calculation in Excel that is Mark Calculation. Anyone can present a suitable process for dynamically calculation row wise.

Actually, recently I completed individual process by hardcore in process task. Please check my excel and automation file…

image
image1366×768 71.1 KB

image
image1366×768 85.7 KB

My Expectation is -
B2+C2= Total Value
B3+C3= Total Value
B4+C4= Total Value… but how to dynamically not hard coded or fixed cell value.

2

Hi @ARahman,
You can try it as below.

  1. Read the entire excel file using workbook ReadRange → output would be dtExcelRead
  2. Take int i=2
  3. while i<=dtExcelRead.row.count
    writeCell Activity → Range should be : “D”+i.ToString
    → Value should be : =sum(“B”+i.ToString+“:”+“C”+i.ToString)
    i=i+1
    It will print the formula and generate the sum.

Hope this will work for you.
Thanks!

1 Like
3

so you can enter formulas directly into excel :slight_smile:

you can use a write cell activity and write directly into column D

firstly use read range to read datatable
then determine your last row intRows = (dt.Rows.Count)

In write cell you can write in formula:
“=sum(B2:C2)”
in your range write:
“D2:C”+intRows.ToString

1 Like
4

@Deepak94… How to assign i=2, Can you show my project

Excel_Data_Save.zip (20.7 KB)

5

Hi @ARahman,
You can simply create a new int variable and name it as “i”.
then you have to assign i=2 before while loop.

Thanks!

2 Likes
6

You don’t need to loop @ARahman. Use write cell activity to pass the formula for one row (may be the second row) and then use auto fill range activity which will write the data in all the rows of the excel upto whatever rows you have data in excel

2 Likes
7

Hi @HareeshMR… Can you explain it more description. Or please correction in my workflow that i attached before.

8

@ARahman

Steps to do

  1. Use assign activity and declare the variable of type string and assign the value as “=Sum(A2:B2)”
  2. Use excel application scope and provide the path of excel
  3. Then write cell Activity to write the formula in that one cell which will sum A2 and B2 cell
  4. Then use auto fill range to drag the formula to the end of the excel sheet data
3 Likes
9

Thanks @HareeshMR. The Problem has been Solved as follow your Steps.

2 Likes
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.