Write cell into next empty cell

Help Studio
1

Hi,

I’m having an issue writing a variable into the next empty cell.

I need to get numeric date out of SAP every morning and put it in the right field in column B. So for 1st of April I want it to automatically fill in Cell B458. Then when I want to run the robot tomorrow it needs to fill it in in B459 etc. I created a variable for the SAP data so all I need is a formula to copy it in the next empty cell.

I checked the other topics about this on the forum but both read range and write cell don’t seem to work. Can anyone help?

image

Warm regards,

Nick

2

Hi @Nick_van_den_Berg

u can create a excel file which store the cell number on which u had written yesterady and then read the excel again, and retrive it and increment it by one and write in appropriate cell under excel B column .

3

Hi Nived,

is there no other way then this? Can I not look for it with a formula in Write Cell activity?

Warm regards,

Nick

4

Hi @Nick_van_den_Berg
another way is u can check the dates in A column

like when u run ur workflow everyday, read the excel and store in dt1

then use lookup datatable to lookup the following value in A column
Now.ToString(“dd/MMM”)

then output of lookup datatable would be the rowindex of that particular row , let say rowindex store the rowindex

now use write cell activity as

Cell : “B”+(rowindex+2).ToString

or instead of write cell , use the assign activity as below

dt1.Rows(rowindex)(1) = variable (variable from sap)

and then use write range to write the data

Hope the idea helps you

Regards,
Nived N
Happy Automation

5

Hi @Nick_van_den_Berg - Just making sure, you are running this is in Studio or StudioX?

6

Studio :slight_smile:

7

@Nick_van_den_Berg -

idxLastValue = Dt.AsEnumerable.ToList.FindLastIndex(Function (x) Not (isNothing(x(“Column1”)) OrElse String.IsNullOrEmpty(x(“Column1”).toString.Trim)))

Above Here Column1 is the name of your column.

image
image209×532 4.79 KB

For the above example: it will return 15 (because A2 is index 0)…

So you can use write cell = “A” + (idxLastValue+3).tostring ==> This will give you A18…

Everyday when you run it, this works automatically.

8

Hi,

I’ve tried this but it doesn’t seem to recognize idxLastValue.

Can you send an example?

Warm regards,

Nick

9

Hi Nick,

If the intention is to always fill the next empty row based on ColumnB.
What you could do is before you generate

Variables Needed
Create a new variable = {lastRow}
DataTable Variable from your Excel File = {dtSAPDate}

You can first Assign your variable
lastRow = dtSapDate.Rows.Count.ToString
~This basically gives you a string value of the last row, in your example, the case shall be “457”.

After which, your write range could be as per
“B” + (Cint(lastRow) + 1).ToString

Which basically gives you “B458”, and this can be repeated the next day, after which the robot will give you a new lastRow number if it has been populated the day before.

Do inform me if this works for you.

10

Hi,

if I create this formula this is the error I get: Excel Application Scope: Object reference not set to an instance of an object.

Warm regards,

Nick

11

@Nick_van_den_Berg - Please find the starter help here. Write Cell After Empty cell_NVDB.zip (49.9 KB)

12

Hi Nick,

Can I get a sample of your Project?
Would allow it to be easier to check.

Thank you.

13

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