Using the Range variable (2000/2002/2003)

Topic

New Reply

Can anyone either provide a link to or an example of code for instantiating and using a Range object in Excel? I’m trying to create a macro that will instantiate a range object and assign to it a range of cells selected on the worksheet. It’s been a while since I did any VBA code in Excel so I’m having trouble remebering hwo to do this but I do recall that you use the Range object when working with a selection of more then cell. For the life of me I can’t figure out what I’m doing wrong and the on line help in Excel for the Range variable is of no use as far as I can tell.

To give you an idea of what I’m ultimately trying to do:

1) User select a range of cells; could be 2 or more in the same row or 2 or more in the same column.
2) User clicks button on worksheet to execute macro
3) Macro instantiates Range objeect and assigns it’s the currently selected cells.
This is where I can’t go any further. Every attempt to try and assign a Range objects range fails.

ANy help is very muych appreciated. I’m sure that once I see a good example I’ll be tapping my forehaed and sayin ‘but of course thats how you do it’.

Thanks
Bubba

Reply | Quote

Replies

It is quite simple:

Dim rng As Range
Set rng = Selection

Reply | Quote

Is this what you want:


Dim oRng As Range
Set oRng = Selection


Reply | Quote

Thanks to both of you forprovindg this simple example. I knew it was doable and not hard. What I don;t get is why the code I was using wouldn’t work. I had the code set like this:

Dim rPins As Range
Set rPins = Range(Selection, Selection.End(xlToRight)).Select

If I break out the select and range asigment as separte actions then they work:

Dim rPins As Range
Range(Selection, Selection.End(xlToRight)).Select
Set rPins = Selection

Why does my first code example fail?

Now that I’ve got help with this I;d like to ask one more question. Once I have the range assigned what is the best way to iterate through the range and take the value of each cell in the range and so something with? For example if I wanted to take the values (all text) in each cell within the range and concatenate them what would be an example of how to do that.

Thansk again guys!

Reply | Quote

In the first place, if you want to assign the current selection to a Range variable, there is no need to use Range(Selection, Selection.End(xlToRight)). Selection itself is a Range object that represents the current selection.
In the second place, the instruction

Set rPins = Range(Selection, Selection.End(xlToRight)).Select

tries to do too much: it mixes selecting a range and assigning a variable. The part to the right of the =, namely Range(Selection, Selection.End(xlToRight)).Select, doesn’t evaluate to a Range object, it selects a range. You can’t use the syntax

Set variable = (some kind of action)

About your second question:

Dim rPins As Range
Dim rCell As Range
Dim strResult As String

Set rPins = Selection

For Each rCell In rPins
strResult = strResult & rCell
Next rCell

MsgBox strResult

In the line For Each rCell in rPins, we implicitly use rPins.Cells, and in strResult = strResult & rCell, we implicitly use rCell.Value.

Reply | Quote

To add one additional comment to what Hans said, you don’t need to use a separate variable to iterate through the cells in the current selection (even though that is what your original question asked how to do). The code could be simplified to:


Dim rCell As Range
Dim strResult As String
For Each rCell In Selection
strResult = strResult & rCell
Next rCell
MsgBox strResult


Reply | Quote