User input as a range

[Shane]

Hello,
I need to get user input for a range of data and then assign this range to
array.

Anyone got any pointers..
Thanks S

 

[Tom Ogilvy]

Sub Tester9()
Dim varr As Variant
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("select range with mouse", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
varr = rng.Value
For i = 1 To UBound(varr, 1)
For j = 1 To UBound(varr, 2)
Debug.Print "Varr(" & i & ", " & j & ") = " & varr(i, j)
Next
Next
End Sub

 

[keepITcool]

there are 2 inputbox method's.
VBA's
Inputbox or VBA.inputbox

and Excel's Application.Inputbox

The latter boasts a refedit.. when Type =8

dim rng as range
on error resume next
set rng = Application.Inputbox("Select a Range..",type:=8)
on error goto 0
if rng is nothing then
exit sub 'cancelled
else
msgbox rng.address
endif



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >