Getting cell address from input box

[vandenberg p]

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

 

[Bob Phillips]

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)

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?",

 

[vandenberg p]

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

: 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)


: :>
:> 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

 

[Bob Phillips]

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)

 

[Bob Phillips]

Pieter,

Try this then .

Note the variable declarations, they are important

Dim loopc As Long
Dim userrange() As Range
Dim rng As Range

ReDim userrange(1 To 10)
On Error Resume Next
Set rng = Application.InputBox _
(Prompt:="Input Cell: Select by clicking on the cell you wish to use",
_
Title:="Select Input Cell", _
Default:="Select Cell", _
Type:=8)
On Error GoTo 0
If IsError(rng) Then
MsgBox "Cell has an error"
ElseIf rng Is Nothing Then
MsgBox "User cancelled"
Else
Set userrange(loopc) = rng
End If


--

HTH

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