Code to Assist with Nested Function, Also Using ISERROR

[Paige]

My code has a function that equates to Lotus 123's XINDEX function; it works
fine. I need to use the ISERROR with the formula in the cell that uses this
function tho, so that when an error is returned, the cell shows blank; but
when I add it, it goes over the 7 nested function limit. Note: This formula
is in a cell that will also need to be copied and pasted down the column, so
the formula references have to change (i.e., it is not a one cell thing that
I could break up with a range name for example). The code for the XINDEX
function is:

Public Function xIndex(vRange, vRowVal, vColVal)
On Error GoTo xIndexErr
Application.Volatile
With Application.WorksheetFunction
xIndex = .Index(vRange, .Match(vColVal, vRange.Columns(1), 0),
..Match(vRowVal, vRange.Rows(1), 0))
End With
Exit Function
xIndexErr:
xIndex = CVErr(Err)
End Function

My formula is:
=IF(P12=5,"",IF(M12=9,AU12,IF(M12=10,xINDEX(SMB,M12,H12),IF(M12<5,xINDEX(SNT,M12,H12),IF(M12<9,xINDEX(SNTOS,M12,H12),IF(M12<15,xINDEX(IPS,M12,H12),xINDEX(IPSOS,M12,H12)))))))
The cell references in M and H will change as the formula is copied down the
column; it also has range names (SMB, SNT, SNTOS, IPS, and IPSOS) that tell
Excel where to look up the data.

Is there a way to adjust the VB code for the function so that if the formula
returns an error message, the cell shows blank? Or is there a better way to
do this? Thanks for any thoughts/suggestions.

[Excelenator]

According to your last question you could change the line after the "xIndexErr:" to read "xIndex = "" " or "xIndex = 0" . This will make the function return a blank or zero instead of an error message. I'm not sure if that will help or hurt your formula.

Public Function xIndex(vRange, vRowVal, vColVal)
On Error GoTo xIndexErr
Application.Volatile
With Application.WorksheetFunction
xIndex = .Index(vRange, .Match(vColVal, vRange.Columns(1), 0),
..Match(vRowVal, vRange.Rows(1), 0))
End With
Exit Function
xIndexErr:
xIndex = CVErr(Err)
End Function

.....

Is there a way to adjust the VB code for the function so that if the formula
returns an error message, the cell shows blank? Or is there a better way to
do this? Thanks for any thoughts/suggestions.

[Paige]

Thanks; this might work - will give it a try!

"Excelenator" wrote:

>
> According to your last question you could change the line after the
> "xIndexErr:" to read "xIndex = "" " or "xIndex = 0" . This will make
> the function return a blank or zero instead of an error message. I'm
> not sure if that will help or hurt your formula.
>
>
> Paige Wrote:
> >
> > Public Function xIndex(vRange, vRowVal, vColVal)
> > On Error GoTo xIndexErr
> > Application.Volatile
> > With Application.WorksheetFunction
> > xIndex = .Index(vRange, .Match(vColVal, vRange.Columns(1), 0),
> > ..Match(vRowVal, vRange.Rows(1), 0))
> > End With
> > Exit Function
> > xIndexErr:
> > xIndex = CVErr(Err)
> > End Function
> >
> > .....
> >
> > Is there a way to adjust the VB code for the function so that if the
> > formula
> > returns an error message, the cell shows blank? Or is there a better
> > way to
> > do this? Thanks for any thoughts/suggestions.
>
>
> --
> Excelenator
>
>
> ------------------------------------------------------------------------
> Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
> View this thread: http://www.excelforum.com/showthread...hreadid=565399
>
>