Trying to return Range from InputBox
 

Hi,

I'm trying to write a Sub to return a range that will be used later.
want to use an InPutBox to select the desired data then return th
number of columns for later computations. Arun-Time error '424'
object required, is returned regardless of how I define rRange.

My humble code follows:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
numCols = Selection.Columns.Count

MsgBox numCols
End Sub

The data I'm using is
F1 F2 F3
note1 0.13 0.00 0.05
note2 0.13 0.01 0.07
note3 -0.02 0.22 0.12
note4 0.02 0.04 -0.11
note5 0.03 0.23 0.01
note6 -0.01 0.12 -0.02
note7 65.00 -0.08 0.06
note8 0.16 -0.05 0.04
note9 0.01 -0.15 0.22


In the InPutBox box, when prompted I select, using the mouse, B2:D7
Hit OK and the code always fails with the Run-Time '424' error. Th
De-bugger is pointing to

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="PRINT RANGE", Type:=8

--
Message posted from http://www.ExcelForum.com

Trying to return Range from InputBox
 

Hi

You're very close. The code will err when you cancel or write something
illegal, and it will also err if nothing is selected (You're not actually
selecting rRange, so Selection.Columns.Count is not what you should use). As
far as I can tell, this works:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer
On Error Resume Next
Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
If rRange Is Nothing Then Exit Sub
numCols = rRange.Columns.Count
MsgBox numCols
End Sub

HTH. Best wishes Harald

"Jiana " skrev i melding
...
Hi,

I'm trying to write a Sub to return a range that will be used later. I
want to use an InPutBox to select the desired data then return the
number of columns for later computations. Arun-Time error '424' -
object required, is returned regardless of how I define rRange.

My humble code follows:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
numCols = Selection.Columns.Count

MsgBox numCols
End Sub

The data I'm using is
F1 F2 F3
note1 0.13 0.00 0.05
note2 0.13 0.01 0.07
note3 -0.02 0.22 0.12
note4 0.02 0.04 -0.11
note5 0.03 0.23 0.01
note6 -0.01 0.12 -0.02
note7 65.00 -0.08 0.06
note8 0.16 -0.05 0.04
note9 0.01 -0.15 0.22


In the InPutBox box, when prompted I select, using the mouse, B2:D7,
Hit OK and the code always fails with the Run-Time '424' error. The
De-bugger is pointing to

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="PRINT RANGE", Type:=8)


---
Message posted from http://www.ExcelForum.com/

Trying to return Range from InputBox
 

Harald,

Thank you for the suggestion. I ran the code but exits at

If rRange Is Nothing Then Exit Sub

never geeting to definition of numcols

Jiana

Harald Staff wrote:
*Hi

You're very close. The code will err when you cancel or writ
something
illegal, and it will also err if nothing is selected (You're no
actually
selecting rRange, so Selection.Columns.Count is not what you shoul
use). As
far as I can tell, this works:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer
On Error Resume Next
Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
If rRange Is Nothing Then Exit Sub
numCols = rRange.Columns.Count
MsgBox numCols
End Sub

HTH. Best wishes Harald

"Jiana " skrev i melding
...
Hi,

I'm trying to write a Sub to return a range that will be use
later. I
want to use an InPutBox to select the desired data then return the
number of columns for later computations. Arun-Time error '424' -
object required, is returned regardless of how I define rRange.

My humble code follows:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
numCols = Selection.Columns.Count

MsgBox numCols
End Sub

The data I'm using is
F1 F2 F3
note1 0.13 0.00 0.05
note2 0.13 0.01 0.07
note3 -0.02 0.22 0.12
note4 0.02 0.04 -0.11
note5 0.03 0.23 0.01
note6 -0.01 0.12 -0.02
note7 65.00 -0.08 0.06
note8 0.16 -0.05 0.04
note9 0.01 -0.15 0.22


In the InPutBox box, when prompted I select, using the mouse
B2:D7,
Hit OK and the code always fails with the Run-Time '424' error.
The
De-bugger is pointing to

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="PRINT RANGE", Type:=8)


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

Trying to return Range from InputBox
 

What to say ? It works fine here. How are you putting what into the inputbox
? You're supposed to select a range of spreadsheet cells with your mouse
while the box is displaying. Humming the column header letters won't do.
Consider also giving it an initial default value for the hummers and the
ones that select before they run the code (which makes perfect sense in
almost all cases):

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer
On Error Resume Next
Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Default:=Selection.Address, _
Title:="DATA RANGE", Type:=8)
If rRange Is Nothing Then Exit Sub
numCols = rRange.Columns.Count
MsgBox numCols
End Sub

HTH. Best wishes Harald

"Jiana " skrev i melding
...
Harald,

Thank you for the suggestion. I ran the code but exits at

If rRange Is Nothing Then Exit Sub

never geeting to definition of numcols

Jiana

Harald Staff wrote:
*Hi

You're very close. The code will err when you cancel or write
something
illegal, and it will also err if nothing is selected (You're not
actually
selecting rRange, so Selection.Columns.Count is not what you should
use). As
far as I can tell, this works:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer
On Error Resume Next
Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
If rRange Is Nothing Then Exit Sub
numCols = rRange.Columns.Count
MsgBox numCols
End Sub

HTH. Best wishes Harald

"Jiana " skrev i melding
...
Hi,

I'm trying to write a Sub to return a range that will be used
later. I
want to use an InPutBox to select the desired data then return the
number of columns for later computations. Arun-Time error '424' -
object required, is returned regardless of how I define rRange.

My humble code follows:

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="DATA RANGE", Type:=8)
numCols = Selection.Columns.Count

MsgBox numCols
End Sub

The data I'm using is
F1 F2 F3
note1 0.13 0.00 0.05
note2 0.13 0.01 0.07
note3 -0.02 0.22 0.12
note4 0.02 0.04 -0.11
note5 0.03 0.23 0.01
note6 -0.01 0.12 -0.02
note7 65.00 -0.08 0.06
note8 0.16 -0.05 0.04
note9 0.01 -0.15 0.22


In the InPutBox box, when prompted I select, using the mouse,
B2:D7,
Hit OK and the code always fails with the Run-Time '424' error.
The
De-bugger is pointing to

Set rRange = Application.InputBox _
(Prompt:="Select data range", _
Title:="PRINT RANGE", Type:=8)


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/