Wow is this frustrating. Excel manipulation, HELP

Help
,
1

I’m betting this is simply because I don’t know all the nuances yet. I have a spreadsheet that has many rows of data in about 13 columns. 1/2 the columns have 2 rows of formulas at the bottom. The rows are daily account totals. Each day I want to be able to add a new row to the bottom of the list above the formulas. to do that I take the last two rows of formulas and copy them down so the relative cell references all increase to include the new row. Seems like using a read range write range would do nicely but it only brings the values not the formulas and it keeps the formating of the row that was already there not the formating of the row being moved. I tried all sorts of hot keys and they were incredibly unreliable. I would have it working then try it a day later and it wouldn’t and I didn’t change anything that I know of. I thought I would try inserting a row which would move things down but I find insert column not insert row. HELP!!! This can’t be that hard to do, I’m probably just making it harder than it needs to be.

2

Can you send me a sample spreadsheet with your required formulae in it…?

@billpennock

2 Likes
3

I need to change the data in it because the current data is confidential. I’ll try to do that today and upload it here. I’ll put two sheets in it. one with the before bot and one with the results I’m looking for. I did try something new last night that I’m going to work out the rest of the way today that shows promise. This is all about finding the best way from a myriad of possibilities.

4

hey,
I was thinking, because you can I believe write a formula in with the excel write range activities. So you could potentially just read the table in without the formula row, then add the formulas in at the bottom.

Then, you just need a formula that doesn’t rely on a static row number, or increment the row for each new line added.

I hope you find your ideal answer though.

Thanks.

5

thanks for the thoughts. I found the sequence that works reliably. I had a lot of trouble with hotkeys like ctrl-c and ctrl-v working reliably. read range write range only reads and writes values as far as I can tell (if this is wrong then I’d love to know how to change it to read and write formulas). I had started by trying to mimic what I, the human, have been doing for months. That was not working because of the things mentioned. Finally I hit on selecting the bottom row. Using click actions to copy the row and then using insert to insert the copied cells and click on the radio button for move the current cells down. This also automatically changed the values for the named range that I was using to know what row to copy so it always becomes the last row. That was a bunch of click activities, especially since hitting the “copy” button works in the human world but is not reliable to UiPath. Hitting the down arrow next to copy though and then hitting the copy in that menu is. Then I ran into little things like excel didn’t always open with the tab I wanted as the default tab. I don’t know why but it didn’t. That just meant adding a click action at the start that set excel where I wanted the starting point. Finally using string manipulation I am able to write into the cells i need by saving the first cell of the last row range using substring and then, because I know where that is in relation to every cell I want to write, i can increment the column alphas or the row numbers to where I want them. This is now working reliably. Next step, get on the web and get the values I want in the cells.