Solved: Select Range with variables

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

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]

Why don't you just use :-

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

Quote:

---

Originally Posted by unmarkedhelicopter

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

Quote:

---

Originally Posted by 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

---

Try copyrange = Range(x:y)

Quote:

---

Originally Posted by johnske

Try copyrange = Range(x:y)

---

doesnt work....:dunno

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

Quote:

---

Originally Posted by johnske

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

---

jep, did that. very strange....

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

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]

????
[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. :thumb

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

????

Quote:

---

Originally Posted by unmarkedhelicopter

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

[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 :)

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

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

so what changed ?