Code to Assist with Nested Function, Also Using ISERROR
 

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,M1 2,H12),IF(M12<5,xINDEX(SNT,M12,H12),IF(M12<9,xINDE X(SNTOS,M12,H12),IF(M12<15,xINDEX(IPS,M12,H12),xIN DEX(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.

Code to Assist with Nested Function, Also Using ISERROR
 

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

Code to Assist with Nested Function, Also Using ISERROR
 

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