Add a formula to the last cell of a column in Excel

[RandomGuy]

Hey guys!

In my process I draw an Excel file from SAP and if I do it on different days the data changes a little bit. For example on one day it has 300 rows and on the other day it has 1000.
Now I want to add a formula to the last row of the column in the excel sheet. Is there a way to do it? I am not allowed to use any external packages like “BalaReva” whick makes it harder

Any solutions?

Best regards!

[urvesh.mistry]

Hello @RandomGuy,

Try to get the total rows count (“YourDataTableVariable”.Rows.Count) of an excel and then user write cell activity to write the formula.

Thanks & Regards,
Urvesh Mistry

[RandomGuy]

Hi @urvesh.mistry,
thanks for your answer! I forgot to mention that I need to put the formular below the last row of the excel file. For example the last data is in row 300 and the formula (subtotal) needs to be in row 301.
Do you have an idea?

[urvesh.mistry]

Hello @RandomGuy,

Perform the same operation with the little changes, just do the below mentioned changes.

i.e. (“YourDataTableVariable”.Rows.Count + 1)

I hope this will help you.

Thanks & Regards,
Urvesh Mistry

[RandomGuy]

I wrote “Y”+dataTable.Rows.Count.ToString +1" but it says “Rows is not a member of String”
Did I do something wrong?

[urvesh.mistry]

Hello @RandomGuy,

Yes, you wrote it in wrong way

Type Like this : “Y”+(dataTable.Rows.Count + 1).ToString

This will solve your problem.

Thanks & Regards,
Urvesh Mistry

[RandomGuy]

@urvesh.mistry
Aaaah okay now there is no error. The problem is still that I have to type in the formula in UiPath, basically: (=SUBTOTAL(9;Y2:Ylastrow +1). I don’t know the last row or the range of the column in advance. Do you have a solution for that?

[prasath17 (Guru)]

@RandomGuy - Here you go

Output:

Mark this as solution, if it solves your query.

[RandomGuy]

@prasath17 And if you wrote “=SUM(A1:A5)” in the formula would it still calculate all rows and put into the last empty cell?

[prasath17 (Guru)]

@RandomGuy - Yes , it worked…

you could have tried by yourself? did you?

[RandomGuy]

@prasath17 What about the Assign activity? What does “DestRow” stand for? Is it a variable? If yes, what value does it have?

[RandomGuy]

I did the way you wrote, but it only calculates the sum of Y2:Y4 but I need the whole column. The formula which stands in the cell (=SUM(Y2:Y4)) will be in Excel too but I need the formula “(=SUBTOTAL(9;Y2:Ylastrowofthecolumn)”. Do you know what I mean?

[RandomGuy]

@prasath17 @urvesh.mistry
Also it is not only the subtotal formula I use for this principle but others like VLOOKUP as well.

[prasath17 (Guru)]

@RandomGuy - Destrow and Actual rows are Int variable. Since I had 5 rows excluding the header it has the value of 5. Since I need to write the output in the A7 column so i added+2 . I have created one more variable real rows to get Rows.count+1

WriteCell

Output

Working flawlessly.

Here is the xaml and excel file : LINQ.xlsx (8.9 KB) Main.xaml (14.3 KB)

[RandomGuy]

@prasath17 That’s cool from you!
But does it work with SUBTOTAL as well?

[prasath17 (Guru)]

@RandomGuy - Please show me what you have tried ? what is the error/result you got?

I see you keep asking questions, without showing the effort you took…
I have already showed you how this stuff works…

[RandomGuy]

@prasath17
Well that’s what I have now. I deleted the variable which I had in the Assign activity but that is not very relevant I guess. The range has to be in column Y and the formula has to be in its last row. The Subtotal formula needs to know which range it should take. That’s why I have the Y+(dt.Rows.Count+1).ToString+" part. The fun thing is that it works when I change the formula to SUM which doesn’t make sense to me since the range stays the same.
Here is a screenshot:

[prasath17 (Guru)]

@RandomGuy - Its working…Your Formula is wrong, I think you are having Semi Colon after the 9, that’s causing the problem.

Write Cell

Please mark this as solution, if it solves your query.