Range as Variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lwm - 26 Jan 2008 20:45 GMT

I want to take the current range and assign it to a variable touse in
additional functions.

How do I assign the current range to a variable like X?

Thanks

Reply to this Message

Alan Beban - 26 Jan 2008 20:58 GMT

> I want to take the current range and assign it to a variable touse in
> additional functions.
>
> How do I assign the current range to a variable like X?
>
> Thanks

Dim X As Range
X = Range("A1:B4")

Reply to this Message

Otto Moehrbach - 27 Jan 2008 02:34 GMT

Try:
Set X = Range("A1:B4")

>> I want to take the current range and assign it to a variable touse in
>> additional functions.
[quoted text clipped - 4 lines]
> Dim X As Range
> X = Range("A1:B4")

Reply to this Message

FSt1 - 26 Jan 2008 21:01 GMT

hi
current range?????
dim x as range
set x = range("A1:L100")'set to current range

regards
FSt1

> I want to take the current range and assign it to a variable touse in
> additional functions.
>
> How do I assign the current range to a variable like X?
>
> Thanks

Reply to this Message

FSt1 - 26 Jan 2008 21:03 GMT

or..
you can highlight a range....
dim x as range
set x = selection
msgbox x.address

regards
FSt1

> hi
> current range?????
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

lwm - 26 Jan 2008 21:46 GMT

Set x = selection does not seem to work. I want a currently selected area
that I will not know in advance. So it must take the currently selected area
and save that to a variable.

x = what ever the current selction is.

> or..
> you can highlight a range....
[quoted text clipped - 19 lines]
> > >
> > > Thanks

Reply to this Message

FSt1 - 26 Jan 2008 22:17 GMT

hi
it worked in 2003. I retested to make sure. here is the copy and paste from
my vb editor..
Sub test1()
Dim x As Range
Set x = Selection
MsgBox x.Address
End Sub
x is the variable. make sure you pre-select the range before running the
macro.

regards
FSt1

> Set x = selection does not seem to work. I want a currently selected area
> that I will not know in advance. So it must take the currently selected area
[quoted text clipped - 25 lines]
> > > >
> > > > Thanks

Reply to this Message

lwm - 26 Jan 2008 22:51 GMT

When I do this

Set x = Selection

Application.Dialogs(xlDialogActiveCellFont).Show Arg3:=0
'
Selection.AutoFill Destination:=ActiveCell.Range(x), Type:=xlFillDefault

this fails

if I do x = "a1:L1" it works.

I am trying to have the ability to use whatever range the user has already
selected. If I hard code it then I can't allow changes.

> hi
> it worked in 2003. I retested to make sure. here is the copy and paste from
[quoted text clipped - 39 lines]
> > > > >
> > > > > Thanks

Reply to this Message

FSt1 - 26 Jan 2008 23:37 GMT

hi
the code i supplied does assign the select range to x. which is what you
asked for.
your are getting an application define error on this line
Selection.AutoFill Destination:=ActiveCell.Range(x), Type:=xlFillDefault

what are you trying to do?

regards
FSt1

> When I do this
>
[quoted text clipped - 54 lines]
> > > > > >
> > > > > > Thanks

Reply to this Message

FSt1 - 27 Jan 2008 00:07 GMT

hi,
after looking at what your code is trying to do, i am guessing that you are
trying to set the font size in the selected range.
try this....
Sub test1()
Dim x As Range 'not needed
Set x = Selection 'not needed
MsgBox x.Address 'not needed
dim fs as long
Application.Dialogs(xlDialogActiveCellFont).Show Arg3:=0
fs = ActiveCell.Font.Size
With Selection.Font
.Size = fs
End With
End Sub

regards
FSt1

> hi
> the code i supplied does assign the select range to x. which is what you
[quoted text clipped - 65 lines]
> > > > > > >
> > > > > > > Thanks

Reply to this Message

Dave Peterson - 27 Jan 2008 00:43 GMT

x is already a range.

I don't know what this is supposed to do.
ActiveCell.Range(x)
But you don't usually wrap Range() around a range object variable.

And activell.range() is sometimes useful, but I'm not sure that's what you
really want.

You may want to state your intent in plain old words.

> When I do this
>
[quoted text clipped - 54 lines]
> > > > > >
> > > > > > Thanks

Signature

Dave Peterson

Reply to this Message

JLGWhiz - 27 Jan 2008 03:51 GMT

I think what you are looking for is the following two lines:

x = ActiveSheet.Selection.Address
myVar = Range(x)

myVar will now contain a range reference, whether it be one cell or a group
of many cells. You can use it anywhere else in the code and it will refer to
original selected range.

> I want to take the current range and assign it to a variable touse in
> additional functions.
>
> How do I assign the current range to a variable like X?
>
> Thanks

Reply to this Message

lwm - 27 Jan 2008 18:22 GMT

Thank you all.

I will re think this and post a new question later.

Again thanks for your assistance.

> I think what you are looking for is the following two lines:
>
[quoted text clipped - 11 lines]
> >
> > Thanks

Reply to this Message

Hansolo - 27 Jan 2008 05:21 GMT

Different depending on the the # of dimensions. Looks like you want at least
a two-dimensional array which requires a variant.

You could try either one of the following.

Sub RangeToVaiant()
'This is one way to get a selected range into a varient array.

Dim x As Variant

x = ActiveWindow.RangeSelection.Value

MsgBox UBound(x, 1)
MsgBox UBound(x, 2)

End Sub

Sub RangeToVarient2()
'This is another way to get a selected range into a varient array

Dim x As Variant
Dim WorkRange As Range

Set WorkRange = Selection
x = WorkRange

MsgBox UBound(x, 1)
MsgBox UBound(x, 2)
End Sub

Signature

May the force be with you!

> I want to take the current range and assign it to a variable touse in
> additional functions.
>
> How do I assign the current range to a variable like X?
>
> Thanks

Reply to this Message

James - 27 Jan 2008 19:11 GMT

G'Day LWM,

I'm new to this Excel newsgroup stuff but let me know if this helps you out.

Firstly you will need to know what the range cell address is in the
worksheet. For this example I am going to use the first worksheet and the
entire column A:A.

It is always a good idea to declare all your variables and objects
explicitly in your code so try and use the following:

Dim ws As Worksheet
Dim X As Range
Set ws = Worksheets(1)
Set X = ws.[A:A]

Hope this helps. You should now be able to loop through the range using the
For Each Next statement.

Cheers

James

> I want to take the current range and assign it to a variable touse in
> additional functions.
>
> How do I assign the current range to a variable like X?
>
> Thanks

Reply to this Message