Range as Variable

[lwm]

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

 

[Alan Beban]

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")

 

[FSt1]

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

regards
FSt1

 

[FSt1]

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

regards
FSt1

 

[lwm]

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.

 

[FSt1]

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

 

[lwm]

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.

 

[FSt1]

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

 

[FSt1]

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

 

[Dave Peterson]

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.

 

[Otto Moehrbach]

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

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

 

[JLGWhiz]

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.

 

[Hansolo]

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

 

[lwm]

Thank you all.

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

Again thanks for your assistance.

 

[James]

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