Solved: Select Range with variables

[grohm]

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]

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]

Why don't you just use :-

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

[grohm]

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]

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]

Try copyrange = Range(x:y)

doesnt work....:dunno

[johnske]

Outside that snippet of code - have you declared CopyRange As Range?

[grohm]

Outside that snippet of code - have you declared CopyRange As Range?


jep, did that. very strange....

[Charlize]

?Sub select_range()
Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", Type:=8).Address
Range(sRange).Select
End Sub

[johnske]

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]

????
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]

????
????
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]

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]

What format of coordinate are you entering into the Input box?
A2 or 2,2

[grohm]

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]

so what changed ?