Get Date from Week number

Help
1

Hello!

I want to get the date from sunday from the week in the cell.

image
image992×157 5.01 KB

I tried it with this formula: =DATE((YEAR(NOW()));1;1)- WEEKDAY(DATE((YEAR(NOW()));1;1);2) + C2*7 but I always get the error “Write range: the range does not exist”.
But when I insert the formula in Excel it works… and when I want to write something else in the cell it works too… So I don’t know what the problem is :thinking:
And maybe I have to say that the weeknumber isn’t always 31…It’s different in every file…

image
image504×635 15.2 KB

2

Hello @Loons,
I think formula is ok. “Write range: the range does not exist” means there is something wrong with “AD2” in write cell activity

3

Okay, I tried to write it into another file…
But now I got an error in my Excel file…

image

4

Hi @Loons

Can you first try the write cell activity with a simple string. Just to make sure whether it writes that on the same cell. If that works, then we might need to consider the formula we are trying to type in

5

Hi Lahiru,
I have the same problem, I would like your help.
If you say that the problem is in the formula, what path do you recommend for the solution.
This is my Vlookup formula
image

6

@aferreira054 have you tried switching between excel write cell and workbook write cell? I’ve noticed sometimes one works while the other doesn’t for unknown reasons

7

I have tried both ways and in the excel write cell it throws me an error that says that the writing cannot be executed because there is an excel working in the background

8
9

@aferreira054 - with that error have you tried killing all excel processes before you run?

10

Yes, I already did it and the vlookup formula in excel does not impact
image

11

@aferreira054 can you upload the .XAML and the .xlsx so I can take a look?

Also, have you tried writing something simple to the same cell instead of a formula?

1 Like
12

Vlookup.zip (54.6 KB)
These are my files.
Yes, I wrote something simple as a text and that does impact on the excel and write in all the cells

13

Just a real quick look and haven’t tested anything yet, but right now you are supplying an invalid cell in the Write cell activity. You are supplying a range of cells. You need to specify one individual cell. So instead of I6:I + rowCount you need to just say “I6”. Or if you want to write multiple cells then you should use write range instead

14

I do not use the write range because I just want to write a formula in several cells of a column, it is not a table, if I use write range I must pass it a data table and I do not look for that.
I would like you to try it and give me your feedback please.

15

@aferreira054 - you can’t pass a range of cells to the write range activity. You must loop through and write cell individually, or else you must use the write range activity. It is quite simple to build a datatable with a single column containing the data, then pass that datatable to the write range activity.

Just a simple, but longer processing time (since you have to write to excel n times, where n = number of rows) you can do the loop method to write to each cell individually. You could do for each ExcelRowNum in Enumerable.Range(StartRowInteger,EndRowInteger) then pass in the ExcelRowNum variable as the number in your write cell activity

16

Could you check the file that I sent you and modify it according to your thoughts, then share me please, if it doesn’t bother you so much.

17

I tried running it once before and received errors pertaining to the excel file. There are duplicate headers. I will go ahead and try to decipher it and post a .xaml showing how to do both the write cell and the write range way of doing it.

18

Thank you very much, i´ll wait your response.

19

@aferreira054 - here you go. I wrote a few annotations in there but this should be working fine. Please let me know if you have any questions

daveSolution.xaml (24.5 KB)

1 Like
20

Thank you for the prompt response, the solution works very well!

1 Like