Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Using the Range variable (2000/2002/2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using the Range variable (2000/2002/2003)

    It is quite simple:

    Dim rng As Range
    Set rng = Selection

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using the Range variable (2000/2002/2003)

    Is this what you want:

    <code>
    Dim oRng As Range
    Set oRng = Selection
    </code>
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using the Range variable (2000/2002/2003)

    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!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using the Range variable (2000/2002/2003)

    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:
    <code>
    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
    </code>
    In the line For Each rCell in rPins, we implicitly use rPins.Cells, and in strResult = strResult & rCell, we implicitly use rCell.Value.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using the Range variable (2000/2002/2003)

    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:

    <code>
    Dim rCell As Range
    Dim strResult As String
    For Each rCell In Selection
    strResult = strResult & rCell
    Next rCell
    MsgBox strResult
    </code>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •