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....

[VBA]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[/VBA]

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

cheers

ben

[mdmackillop]

You need to get the numerical elements of the coordinates
[VBA]
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

[/VBA]

[unmarkedhelicopter]

Why don't you just use :-

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

[grohm]

Why don't you just use :-

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

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....

[vba]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[/vba]

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

cheers

ben

Try copyrange = Range(x:y)

[grohm]

Try copyrange = Range(x:y)

doesnt work....

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

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

[johnske]

This works[VBA]
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
[/VBA]

[unmarkedhelicopter]

????
[vba]Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address
Range(sRange).Select
[/vba]

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

How the hell do you delete a posting here ?

[Charlize]

????

????
[vba]Dim sRange As String
sRange = Application.InputBox("Get Range ", "Title", , , , , , 8).Address
Range(sRange).Select
[/vba]

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]

[vba]Range(Application.InputBox("Get Range ", "Title", , , , , , 2)).Select[/vba]will 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 ?