PDA

View Full Version : Solved: Select Range with variables



grohm
06-05-2007, 04:06 AM
Hi Guys

Basically i just want a range which is not set, but can be defined by 2 inputboxes (one for the (X:y) coordinate and one for the (x:Y) coordinate and then automatically get selected.

heres my code so far. i just can not define the range through those inpboxes....

Dim x As String
Dim y as String

x = InputBox("please enter first range coordinate")
y = InputBox("please enter 2nd range coordinate")

copyrange = (x:y) 'this is, where it doesnt work)


With Worksheets("sheet1")
copyrange.Select
End With

it must be possible somehow....thanks for having a look, guys!

cheers

ben

mdmackillop
06-05-2007, 04:27 AM
You need to get the numerical elements of the coordinates

Sub GetRange()
Dim x As String
Dim y As String
Dim Rng1 As Range
Dim Rng2 As Range

x = InputBox("please enter first range coordinate")
y = InputBox("please enter 2nd range coordinate")

Set Rng1 = Cells(CLng(Split(x, ",")(0)), CLng(Split(x, ",")(1)))
Set Rng2 = Cells(CLng(Split(y, ",")(0)), CLng(Split(y, ",")(1)))
'for debug
Range(Rng1, Rng2).Select
Range(Rng1, Rng2).Copy
End Sub

unmarkedhelicopter
06-05-2007, 04:33 AM
Why don't you just use :-

Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address

grohm
06-05-2007, 04:52 AM
Why don't you just use :-

Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address


i tried that already, but it seems that i cannot select that specifi range which i input....if yes, how??

johnske
06-05-2007, 04:57 AM
Hi Guys

Basically i just want a range which is not set, but can be defined by 2 inputboxes (one for the (X:y) coordinate and one for the (x:Y) coordinate and then automatically get selected.

heres my code so far. i just can not define the range through those inpboxes....

Dim x As String
Dim y as String

x = InputBox("please enter first range coordinate")
y = InputBox("please enter 2nd range coordinate")

copyrange = (x:y) 'this is, where it doesnt work)


With Worksheets("sheet1")
copyrange.Select
End With

it must be possible somehow....thanks for having a look, guys!

cheers

benTry copyrange = Range(x:y)

grohm
06-05-2007, 05:01 AM
Try copyrange = Range(x:y)

doesnt work....:dunno

johnske
06-05-2007, 05:09 AM
Outside that snippet of code - have you declared CopyRange As Range?

grohm
06-05-2007, 05:11 AM
Outside that snippet of code - have you declared CopyRange As Range?


jep, did that. very strange....

Charlize
06-05-2007, 05:14 AM
?Sub select_range()
Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", Type:=8).Address
Range(sRange).Select
End Sub

johnske
06-05-2007, 05:18 AM
This works
Option Explicit

Sub try()
Dim x As String
Dim y As String
Dim copyrange As Range
x = InputBox("please enter first range coordinate")
y = InputBox("please enter 2nd range coordinate")
With Worksheets("sheet1")
Set copyrange = .Range(x, y)
copyrange.Select
End With
End Sub

unmarkedhelicopter
06-05-2007, 05:22 AM
????
Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address
Range(sRange).Select


EDIT:
Oops ! I noticed Charlize has already added the obvious select bit. :thumb

How the hell do you delete a posting here ? :banghead:

Charlize
06-05-2007, 05:28 AM
????
????
Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address
Range(sRange).Select
Wants to select the range based on input. This one selects a range. Just not sure it this is exactly what he wants ...

What do you mean by ????

ps.: noticed that you altered your post. So discard that ???? Already know what you meant.

johnske
06-05-2007, 05:30 AM
Range(Application.InputBox("Get Range ", "Title", , , , , , 2)).Selectwill work also, but I'm guessing grohm's trying to figure out why his original code doesn't work :)

mdmackillop
06-05-2007, 05:34 AM
What format of coordinate are you entering into the Input box?
A2 or 2,2

grohm
06-05-2007, 06:14 AM
thanks to all of you guys. it works now (with both versions) with charlizes add unmarked helicopter's solution works and johnsek's works too. Perfect. Thanks!!!!

unmarkedhelicopter
06-05-2007, 11:19 AM
so what changed ?