Trying to return range from InputBox 1

[jnowles]

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. An error '424' - object required, is returned regardless of how I define rRange.
The error is at Set rRange=...

My humble code follows:

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

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

MsgBox numCols
End Sub

 

[Chance1234]

works fine on my machine

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Chance1234]

hit enter too soon,

copied into a module , hit f5 came up with box, i selected a few cells no problem,

tried typing in a range manually no problem

im using excel 2000,

are you calling this sub from another ? or are you calling form a worksheet or other.



Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Chance1234]

the only way i have got it to error is to hit the cancel button which you can put an If statement in or not have the cancel button there look up the help on the input box

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[PHV]

Anyway I guess you have to replace this:
numCols = Selection.Columns.Count
By this:
numCols = rRange.Columns.Count

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jnowles]

Hi,

Thanks for your suggestions, The Sub always fail for me, I'm running VB version 6.3

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, for example, I select (using the mouse)
B27, Hit OK and the code 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)

 

[xlbo]

VB ????
This is a VBA forum

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

Your posted Sub obviously MUST be part of an excel VBAProject.
If in a VB one, replace Application by the name of your instantiared excel object.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jnowles]

It is a VBA question,
at "About" the VB editor says 'Microsoft Visual Basic'.

 

[Bowers74]

There is Visual Basic and then there is Visual Basic for Applications, which is used in Microsoft Office Automation.

Since I believe that this truly is a VBA question, and that you have also recieved the correct answer (from PHV), I have decided to simplify it for you:

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

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

numCols = [COLOR=red yellow]rRange[/color].Columns.Count

MsgBox numCols
End Sub

This works for me if I enter "A1:C56", the MsgBox returns 3.



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jnowles]

For me, this still fails everytime for me. I'm so frustrated.

 

[Bowers74]

What EXACTLY are you doing . . . Step By Step?

What EXACTLY are you TRYING to do . . . Step by Step?



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jnowles]

What I’m doing……..

- My datafile contains entries in the following format

F1 F2 Fr3 F4 F5
note1 0.13 0.00 0.05 0.07 -0.05
note2 0.13 0.01 0.07 0.05 -0.10
note3 -0.02 0.22 0.12 -0.06 0.25
note4 0.02 0.04 -0.11 0.17 0.19
note5 0.03 0.23 0.01 -0.13 -0.10
note6 -0.01 0.12 -0.02 0.12 0.28
note7 65.00 -0.08 0.06 0.09 -0.01
note8 0.16 -0.05 0.04 -0.02 0.02
note9 0.01 -0.15 0.22 -0.01 -0.17

- Copied the code you sent into Module1

At Sheet1, Tools > Macro > MarkFactor6 >Run

- the inputbox prompt appears and using my mouse I select B2:F8 then hit OK and the Error appears, Run-Time ‘424’

- Selecting De-Bug puts me in the Editor and find the arrow pointing to

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

- I set a Watch,

Watch : Application.InputBox(Prompt:="Select range to print.", Title:="PRINT RANGE", Type:=8)
Value : <Out of context>
Type : Variant/Empty
Context: MF6.MarkFactor6

- Stepped through the statements again, the same error, and in the Watch Window it shows

Value : .013
Type : Variant/Double

- The code submitted
Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer

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

numCols = rRange.Select.Cols.Count
MsgBox numCols

End Sub


What I’m trying to do......

- Capture the range of the data selected in InputBox, in this case B2:F8, then return 5 to numCols so that I can use that value in another step

 

[Bowers74]

The Code that I posted, works for me, so why don't you try using the EXACT code to see if it works for you too?

Just a thought . . . ;-)


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jnowles]

I copied and ran the following. It fails with Run-Time '424' error

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

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

numCols = rRange.Columns.Count

MsgBox numCols
End Sub

 

[Bowers74]

I can only emulate this error if I press Cancel (I should have caught that, sorry ):

Sub MarkFactor6()
Dim rRange As Range
Dim numCols As Integer
[COLOR=red yellow]On Error Resume Next[/color]
Set rRange = Application.InputBox("Select data range", _
        "PRINT RANGE", , , , , , 8)
[COLOR=red yellow]If Err.Number <> 0 Then Exit Sub[/color]
numCols = rRange.Columns.Count
MsgBox numCols
End Sub

I used "Error Trapping" to exit the sub if cancel is pressed.

If you are still getting the error '424', even if you have selected a valid range and pressed OK, then I am at a loss.

I hope this helps . . . (this time)!

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Loomah]

jnowles
Are you always expecting all the data to be selected? Every time?
If so couldn't you just use
R

ange("A1").CurrentRegion.Columns.Count - 1

The -1 is to allow for headers in column A.

This doesn't resolve your input box problem but I, like everyone else who has replied, am at a loss to explain it.

Just one final thought if nothing else works. Try cutting the code down to this

numCols = Application.InputBox("Select data range", _
    "PRINT RANGE", Type:=8).Columns.Count

This still produces an error if you click cancel but you could still use Mike's error handling.

Yes, I'm clutching at straws!!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244