Trying to return Range from InputBox

[Jiana]

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, B27
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

 

[Harald Staff]

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]

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]

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