Dear All:
I am trying to build a project that automatically read the value and write to the next empty cell,
So after I read the value for “CASH”, " ACCOUNT RECEIVABLE “, " OTHER RECEIVABLE” in sheet 2,
How can I assign the column of next empty cell(in this excel, the next empty cell is “D2” , so the column of next empty cell is “D”) as start column, and write down the value for “CASH(startcolumn&2)”, " ACCOUNT RECEIVABLE(startcolumn&3) “, " OTHER RECEIVABLE(startcolumn&4)” in sheet1?
On sheet one, do you always have the month-year as a header for all months or are those added by your automation to the excel worksheet?
A few options, you can firstly read sheet1 into a datatable and then count the number of columns, this will give you the last column.
You could make that more secure and check if the first row of that column is empty, if not move onto the next.
To then type into excel you would simply need to convert the column index / number into the relevant letter.
Hi Tim:
Thank you so much for the reply,
Yes it always has a month-year as a header for all month, that means in “sheet1” after Aug-20 there will be Sep-20, Oct-20…
And this is why I want to assign the column of next empty cell(in this one, it is D2) as a “startcolumn” and then allocate the value read for CASH, ACCOUNT RECEIVABLE and OTHER RECEIVABLE to Startcolumn &2(D2). startcolumn&3(D3) and Startcolumn&4(D4).
Could you please teach me any detail if you have any ideas?
OK so the first thing you need to do is read sheet1 into a datatable (with headers)
You can then use a for each loop with datatable. Columns and datatype is datacolumn.
This will go through each column.
Use an if statement inside the for each to check if the cell on the first row is empty of not.
Something like string.isnullorwhitespace(dt.rows(0).item(item).tostring)
If it returns true you then have the column you need to update. You can use an assign to get the column number using item.ordinal
Item is the datacolumn used inside the for each.
Then take a look at this post to convert that number into the relevant column letter
Another option which may be simpler is to just read the whole datatable, update the data inside the datatable in uipath then overwrite the whole sheet1 starting at range a1.
I am sorry sir,
But could you please give me a screen for better indication ?
thank you in advance
