Range as Variable
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 |
Range as Variable
lwm wrote:
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") |
Range as Variable
hi
current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
or..
you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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" wrote: 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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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" wrote: 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" wrote: 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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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 "lwm" wrote: 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" wrote: 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" wrote: 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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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 "FSt1" wrote: 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 "lwm" wrote: 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" wrote: 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" wrote: 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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 |
Range as Variable
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. lwm wrote: 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" wrote: 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" wrote: 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" wrote: or.. you can highlight a range.... dim x as range set x = selection msgbox x.address regards FSt1 "FSt1" wrote: hi current range????? dim x as range set x = range("A1:L100")'set to current range regards FSt1 "lwm" wrote: 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 -- Dave Peterson |
Range as Variable
Try:
Set X = Range("A1:B4") "Alan Beban" wrote in message ... lwm wrote: 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") |
Range as Variable
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. "lwm" wrote: 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 |
Range as Variable
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 -- May the force be with you! "lwm" wrote: 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 |
Range as Variable
Thank you all.
I will re think this and post a new question later. Again thanks for your assistance. "JLGWhiz" wrote: 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. "lwm" wrote: 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 |
Range as Variable
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 "lwm" wrote in message ... 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 |
All times are GMT +1. The time now is 06:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com