How to prevent system from overwrite cells with #NA value when using for each row

Dear worthy friends,

I am using for each row to find a row with certain value and change something in the other column of the row. for that I am using for each row with the following scheme:

The goal is achieved, however something annoying is happened: after for each row is run and I write the datatable back to the same file, all the rows which were filled in with β€˜#NA’ values are replaced by a number: -2146826246.
Can somebody explain why is this happened and how to prevent it?
*for some reason, I need to let the β€˜#NA’ intact and not be changed if the row is not the one that needs to be modified.
For visual help, hereunder the excel after and before for each row (notice the change in the rows with β€˜#NA’ value:

Before for each row activity:

After for each row activity:

Appreciate your guys input and happy holiday! :slight_smile:


Can you try the following, if possible?

  • Check PreserveFormat property of Read Range Activity.
  • Use File- Workbook - ReadRange Activity.


1 Like

Hei Yoichim

Thank you so much for your input.
The PreserveFormat property indeed solved the problem.
Another questions though:

  1. After the for each row activity, the formula in Excel is wiped out, how to prevent this?
  2. Two extra columns are unwantedly appeared after the process with standard name as β€œcolumn1 and column2”, I dont know how to prevent this as well.
    Once again thank you very much for your help.



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