WorksheetFunction.IfError does not seem to work.

[ice00monster]

So I have this function:

Please Login or Register  to view this content.

xi and yi are a range of values taken from the sheet, x and y are input values. After calculating the array fi, some values may result to zero as intended. My problem appears when I'm trying to calculate fip. As seen there, it would be equal to the inverse of fi, and doing so would force a division by zero error on the zero values as calculated by fi. As seen there, and as a test, the division by zero happens at exactly row 6.

What I needed to happen is that, should the division by zero occur, the operation would return a massive value for that particular row (say, fip would return 1000000). However upon running the code the output in the spreadsheet chucks out a #VALUE! error. What am I doing wrong?

[MrShorty]

The #Value error(s) returned reflect the VBA divide by 0 runtime error. I could be wrong, but I think what is happening here is that Excel's IFERROR() function is not programmed to trap VBA's errors. I think what you need to do is use a VBA based strategy (not using Excel's IFERROR() function) to trap the error. Since you know it will be a divide by 0 error, I would probably just use a simple If..then..Else type structure. Maybe:

Please Login or Register  to view this content.

[ice00monster]

Thanks. Solved the problem with it.