Formula data type?

[JChandler22]

I have a spreadsheet with several array formulas. They pull dates from another worksheet, so if there is no date available, the array formulas return a #DIV/0 error.

I am trying to make it so that if there is an error, it will say "N/a" instead of #DIV/0. Instead of adding IF(ISERROR(... before each array formula, I'm trying to do this in vba to avoid making my array formulas any longer than they already are.

The trouble I'm having is that I can't figure out how to define a variable for the formula check. This is what I have so far, but it's obviously not right since I'm getting an error:

Please Login or Register  to view this content.

Currently, vba highlights the line: "r = ActiveCell.FormulaArray" when I try to run the maco.

Can anyone help me untangle this one?

Thanks!

[rylo]

Hi

How about having the macro create the formula for the relevant range, replace with values, then replace the #DIV/0 in that range with N/a...

Viable???

rylo

[FrankBoston]

Here are my impressions about the code and your problem.
I hope it helps or suggests another direction.

Please Login or Register  to view this content.

Since you have only three formulas, maybe IF(ISERROR(... is best anyway
The array formula remains stable and doesn't need to be altered