It didn't bfor me. Have you declared userrange as a particular variable
type?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vandenberg p" wrote in message
...
Bob:

Thanks for the reply and advice but the real issue is this statement:

Set userrange(loopc) = Application.InputBox _
(Prompt:="Input Cell: Select by clicking on the cell you wish to use",
_
Title:="Select Input Cell", _
default:="Select Cell", _
Type:=8)

This statement generates an error if the user selects a cell that happens
for the moment to have #NA in it (generated by =na()).

What I am trying to achive is to have this statement accept the cell's
value
even if for the moment it is #NA because I need its address regardless of
the
value.

Am I explaining my problem?

Pieter

Bob Phillips wrote:
: Hi Pieter,

: When you snip the code, it would be helpful if you get all extraneous
stuff
: out and add any missing bits (i.e. test it) so that we can just copy and
: test.

: Anyway

: If IsError(Xcell) Then
: MsgBox "cell error"
: ElseIf Xcell = "" Then
: ans = MsgBox("The address you are using appears to be empty. Do you want
to
: continue with the selection?", _
: vbYesNoCancel + vbQuestion, "Cell Selection")
: If ans = vbCancel Then GoTo canceledd
: If ans = vbNo Then GoTo tryagain
: End If


: You also don't test for cancel on the Input box.

: --

: HTH

: RP
: (remove nothere from the email address if mailing direct)


: "vandenberg p" wrote in message
: ...
:
: I have the following code snippets as part of a user input routine. I
Dim
: Userrange as range.
: This routine works fine as long as the cell that user selects has a
value.
: If the cell has
: as error (it is usually #NA) then the routine does not return the
address
: and goes to
: "canceledd". It is possible that the cell might an error code and yet
: still be acceptable,
: usually the error is temporary and goes away on the next re-calc (it is
: actually an IRR calculation
: and sometimes and IRR is impossible to find for a particular set of
inputs
: which occurs about 5%
: of the time in this spreadsheet.)
:
: While it is possible "on the error" to re-calc the sheet and try again
it
: is possible that the
: condition could occur several times in a row so that is not a very
clean
: solution and hard to explain
: to the user why it is necessary to keep trying, particularly when the
: inputbox shows
: the correct address each time.
:
: I also tried to change the "Type:=" to 24 (which is sum of 8 (range)
and
: 16 (error). This results in the
: "if xcell" test being true every time no matter the value of the cell.
:
: Can anyone suggest a solution.
:
: Thank you.
:
: Pieter Vandenberg
:
: ---------code snippet-----
:
: tryagain:
: On Error goto canceledd
: Set userrange(loopc) = Application.InputBox _
: (Prompt:="Input Cell: Select by clicking on the cell you wish to
use",
: _
: Title:="Select Input Cell", _
: default:="Select Cell", _
: Type:=8)
: ...
:
: Set Xcell = userrange(loopc)
:
:
: If Xcell = "" Then
: ans = MsgBox("The address you are using appears to be empty. Do you
want
: to continue with the selection?",
:
: vbYesNoCancel + vbQuestion, "Cell Selection")
: If ans = vbCancel Then GoTo Canceledd
: If ans = vbNo Then GoTo tryagain
:
: ...
:
: canceledd:
: calculate
: goto tryagain