Looping through Excel column only summing and checking values of non #REF or #VALUE Problems


I have a spreadsheet that has various columns of data that I want to iterate through and check their value, some cells have #REF in them because they have links to unavailable spreadsheets.

if I loop through each cell in a column and check it’s value is above an absolute level and that the total value of non 0 values isn’t greater than another value, I have a problem. Because the read cell output of reading an error value if you convert it to a string is -2146826265 which throws off all my absolute value checks and totals.

Does anyone have an idea what to do please. [Errors.zip|attachment

Don’t worry about the looping just want to know how to ignore the error values.
I’ve attached an example.

Errors.zip (18.4 KB)

Thanks in advance

@charliefik With if condition check whether cell values contains any Alphabets or especially #REF. If such values exists skip that row.

I don’t think that will work as if you convert the value returned from the read cell activity to a string it returns -2146826265 (see original message) which obviously doesn’t contain any letters, basically it’s not retrieving the string #REF it’s returning an error value, and I don’t want to just check for that number and then ignore it as I’m dealing with numeric data, what if one of the numbers of actual data was that number by fluke then it would be ignored (I know its a small chance but you can’t just assume it would never happen)

What I did in case anyone has to deal with error values is put an excel iferror function around the formulas in the cells and set the error value to “” that way none of the error values were displayed. It’s probably not the best way but it worked.

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