×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Associate a selected range of cells with a Named Range Object

Associate a selected range of cells with a Named Range Object

Associate a selected range of cells with a Named Range Object

(OP)
In Excel VBA, how do you associate the currently selected cells to a named range object?

    Dim myRange As Range
'    Set myRange = Selection
'    Set myRange = ActiveCell.CurrentRegion

neither of these seemed to work!

RE: Associate a selected range of cells with a Named Range Object

Hi - not sure if there is a way but you can use the Range object eg

For Each one In Range("test")
    MsgBox (one.Value)
Next one

Mutt

RE: Associate a selected range of cells with a Named Range Object

With the cells already selected try

    ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=Selection

RE: Associate a selected range of cells with a Named Range Object

Sorry - misread - try this for some ideas :

Sub test()

myrange = ActiveWindow.RangeSelection.Style
MsgBox (myrange)

myrange1 = ActiveWindow.RangeSelection.Value
For Each one In myrange1
    MsgBox (one)
Next one
'Remark - can use myrange1=Selection - is identical to above as seems to store values only.

myrange2 = ActiveWindow.RangeSelection.Address
MsgBox (myrange2)

End Sub

RE: Associate a selected range of cells with a Named Range Object

If you want to set a range object to the selection, the following should work (although it seems that you have tried it already):

Sub Test()
Dim myRange As Range
    Set myRange = ActiveWindow.Selection
    Debug.Print myRange.Address '(just for checking)
End Sub

It works over here...

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Associate a selected range of cells with a Named Range Object

(OP)
Thanks everyone for the help!

Mutt,

 I kept getting the following error with your approachs:

"Object variable or With block variable not set"

ab123456,

You nailed it on the head, I had tried something similar but did not have the syntax correct!  Still learning this VBA notation

Thanks again

joerd,

Interestingly enough you approach worked using ActiveWindow instead of ActiveCell as I had it (saw the selected range in the debuger window), however, it did not seem to actually assign the range address to myRange.  The reason I say that is that the code following this statement in my Macro using the range did not work with this approach but did with ab123456's.

Thanks again for all the help

RE: Associate a selected range of cells with a Named Range Object

dehnemi,

I have found the best way to learn VBA code is to start the macro recorder going and then carry out the tasks and then examine the code and modify to suit your needs. Thats the way I found the code to solve your problem.

RE: Associate a selected range of cells with a Named Range Object

Glad to see you got sorted out but find strange that you are getting error messages with my code and not seeing the range with Joerds as both ways worked fine in my version of Excel! I am using Excel 2000.

Regards
Mutt

RE: Associate a selected range of cells with a Named Range Object

Mutt,

If he has the "option explicit" checked in the VBE/options, he will see errors. Option Explicit means that all your variables have to be defined (i.e MyRange as Range, ...)

Other than that, I can't see any reason that it would give errors.

Also at the front of the sub (after defining the sub), add the following lines as needed:

Application.ScreenUpDating = False (this puts the operation behind the scene.  Make sure to put it to true at the end of the code or you won't see anything).

Application.DisplayAlerts = False (this hides the error messages)

On Error Resume Next (This allows the sub to continue if an error has occurred).

These three (4 if you include putting the ScreenUpdating to true at the end of the sub) will enable your sub-routine to run faster and hopefully keep running as needed.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources