How to define and select a range

[AG]

I have a group of cells that have been pasted in a worksheet and are now
active (highlighted.)
How do I set them as a range? I could set them as a name on the worksheet
but them would need to delete that name at the end of the macro since the
next time I run the macro, the group of cells would be in a different
location on the sheet.

Dim rng1 As Range
Set rng1 = ???????

If it makes a difference, the group of cells in question is adjacent to
other cells that I do not want to include and are pasted as hyperlinks with
the highlighted cells containing the text display of various addresses.

Then how would I select that range later in the macro?
Range(“rng1”) .Select doesn’t seem to work.

[Norman Jones]

Hi AG,

> Dim rng1 As Range
> Set rng1 = ???????

Try:

Set rng1 = Selection

---
Regards,
Norman



"AG" <AG@discussions.microsoft.com> wrote in message
news:0A7DF3F7-90E4-48D6-92D6-D8413182D628@microsoft.com...
>I have a group of cells that have been pasted in a worksheet and are now
> active (highlighted.)
> How do I set them as a range? I could set them as a name on the worksheet
> but them would need to delete that name at the end of the macro since the
> next time I run the macro, the group of cells would be in a different
> location on the sheet.
>
> Dim rng1 As Range
> Set rng1 = ???????
>
> If it makes a difference, the group of cells in question is adjacent to
> other cells that I do not want to include and are pasted as hyperlinks
> with
> the highlighted cells containing the text display of various addresses.
>
> Then how would I select that range later in the macro?
> Range("rng1") .Select doesn't seem to work.
>

[Ron Rosenfeld]

On Thu, 10 Nov 2005 08:25:06 -0800, "AG" <AG@discussions.microsoft.com> wrote:

>I have a group of cells that have been pasted in a worksheet and are now
>active (highlighted.)
>How do I set them as a range? I could set them as a name on the worksheet
>but them would need to delete that name at the end of the macro since the
>next time I run the macro, the group of cells would be in a different
>location on the sheet.
>
>Dim rng1 As Range
>Set rng1 = ???????

Set rng1 = Selection

>If it makes a difference, the group of cells in question is adjacent to
>other cells that I do not want to include and are pasted as hyperlinks with
>the highlighted cells containing the text display of various addresses.
>
>Then how would I select that range later in the macro?
> Range(“rng1”) .Select doesn’t seem to work.

rng1.Select

But note that for most operations in VBA, there is no need to Select the cells
or range.




--ron

[AG]

So far so good but:

rng1.Select gets me a Run-time error '424':
Object required

"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 08:25:06 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >I have a group of cells that have been pasted in a worksheet and are now
> >active (highlighted.)
> >How do I set them as a range? I could set them as a name on the worksheet
> >but them would need to delete that name at the end of the macro since the
> >next time I run the macro, the group of cells would be in a different
> >location on the sheet.
> >
> >Dim rng1 As Range
> >Set rng1 = ???????
>
> Set rng1 = Selection
>
> >If it makes a difference, the group of cells in question is adjacent to
> >other cells that I do not want to include and are pasted as hyperlinks with
> >the highlighted cells containing the text display of various addresses.
> >
> >Then how would I select that range later in the macro?
> > Range(“rng1”) .Select doesn’t seem to work.
>
> rng1.Select
>
> But note that for most operations in VBA, there is no need to Select the cells
> or range.
>
>
>
>
> --ron
>

[AG]

I later want to reference thisn range to use the find method:
rng1.Select
With Selection.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole).Select
End With


"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 08:25:06 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >I have a group of cells that have been pasted in a worksheet and are now
> >active (highlighted.)
> >How do I set them as a range? I could set them as a name on the worksheet
> >but them would need to delete that name at the end of the macro since the
> >next time I run the macro, the group of cells would be in a different
> >location on the sheet.
> >
> >Dim rng1 As Range
> >Set rng1 = ???????
>
> Set rng1 = Selection
>
> >If it makes a difference, the group of cells in question is adjacent to
> >other cells that I do not want to include and are pasted as hyperlinks with
> >the highlighted cells containing the text display of various addresses.
> >
> >Then how would I select that range later in the macro?
> > Range(“rng1”) .Select doesn’t seem to work.
>
> rng1.Select
>
> But note that for most operations in VBA, there is no need to Select the cells
> or range.
>
>
>
>
> --ron
>

[Ron Rosenfeld]

On Thu, 10 Nov 2005 12:38:09 -0800, "AG" <AG@discussions.microsoft.com> wrote:

>So far so good but:

>rng1.Select gets me a Run-time error '424':
>Object required

Following the sequence you outlined in your post, I cannot duplicate that
error. Either you've left something out, or there's code in your routine that
is doing something not apparent in what you've posted so far.


>I later want to reference thisn range to use the find method:
> rng1.Select
> With Selection.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole).Select
> End With
>
>

Why not just:

set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)



--ron

[AG]

I have no doubt that the problem is with my coding.
This is a bit of an education for me.

Using your suggestion
>set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)

When I highlight rng1 I get a comment that rng1 is empty.

Perhaps my initial approach is incorrect. While the sheet I want to define
the range within is active I write:

Dim rng1 As Range
Set rng1 = Selection

So why, later when I reference this variable do I get that message?

Better yet, here’s a brief snippet of the prior & subsequent coding:

‘Opens a workbook named Weekly.xls
Workbooks.Open Filename:="C:\INVEST\TIMING.SYS\Trades\Weekly.xls"
Sheets("Status").Select
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
‘The “x” value functions as a placeholder
'Begins transfer of data a workbook named “Daily.xls”, from a sheet named
“Prices” to Workbook named “Weekly.xls” to a sheet named “Status”
Windows("Daily.xls").Activate
Sheets("Prices").Select
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.Copy
‘The above line is the group of cells that I later want to define as “rng1”
Windows("Weekly.xls").Activate
ActiveCell.Offset(1, 0).Range("A1:A22").Select
Selection.PasteSpecial Paste:=xlValues
ActiveCell.Offset(-23, 1).Range("A1:A22").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(23, 0).Range("A1:A22").Select
ActiveSheet.Paste
‘At this point my group of cells has been pasted and the group is active
(highlighted)

‘With the following 2 lines of code I am assuming that rng1 would be a
declared variable applicable to the workbook “Weekly.xls” within the sheet
Status and available to me for later reference.

Dim rng1 As Range
Set rng1 = Selection

‘Now I continue on with other work
ActiveCell.Offset(-1, -1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1:R1").Select
Selection.PasteSpecial Paste:=xlValue

So later when I try to select this range (rng1) via:
Windows("Weekly.xls").Activate
Sheets("Status").Select
rng1.Select
or alternately, as you suggested:
Set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)

I am clueless as to why I would get a message that the range is empty.





"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 12:38:09 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >So far so good but:
>
> >rng1.Select gets me a Run-time error '424':
> >Object required
>
> Following the sequence you outlined in your post, I cannot duplicate that
> error. Either you've left something out, or there's code in your routine that
> is doing something not apparent in what you've posted so far.
>
>
> >I later want to reference thisn range to use the find method:
> > rng1.Select
> > With Selection.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole).Select
> > End With
> >
> >
>
> Why not just:
>
> set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)
>
>
>
> --ron
>

[Ron Rosenfeld]

On Thu, 10 Nov 2005 15:20:03 -0800, "AG" <AG@discussions.microsoft.com> wrote:

>I have no doubt that the problem is with my coding.
>This is a bit of an education for me.
>
>Using your suggestion
>>set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)
>
>When I highlight rng1 I get a comment that rng1 is empty.
>
>Perhaps my initial approach is incorrect. While the sheet I want to define
>the range within is active I write:
>
> Dim rng1 As Range
> Set rng1 = Selection
>
>So why, later when I reference this variable do I get that message?

I wonder if you have a typo someplace.

Do you have Option Explicit at the top of your macro?

If you do not, put it there.

I wonder if your two "rng1" variables are really the same. I ask because if
you have not initialized a Range variable, the comment should say
"rng1=Nothing" and not "rng1=Empty". "Empty" in your situation should refer to
a Variant type variable that has not been initialized. That can happen if you
have implicitly declared a variable rather than explicitly declaring it.

=====================================
Also, in your code, there is no reason (and it adds to my confusion :-))) to
Select or Activate cells in order to do the kinds of operations you are doing.
You've also go a lot of unnecessary stuff in there, that also makes debugging
very difficult.

For example, this code of yours:
--------------------
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.Copy
--------------------

does the same thing as this code (without Selecting cells):
-------------------------------
Range("A4").End(xlDown).Offset(-2, 0).Copy
-------------------------------

It may be that a lot of your code was generated by recording a macro. While
that's good to get started, it does not generate the most efficient code, and
makes debugging more difficult.


--ron

[AG]

I'll investigate your suggestions and get back to you; thanks for your help.

As to my coding, well what can I say? Thanks for pointing out some of the
inefficiencies; I want to learn and people like you are a great help.

I’ll get back to you on the issue at hand.


"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 15:20:03 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >I have no doubt that the problem is with my coding.
> >This is a bit of an education for me.
> >
> >Using your suggestion
> >>set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)
> >
> >When I highlight rng1 I get a comment that rng1 is empty.
> >
> >Perhaps my initial approach is incorrect. While the sheet I want to define
> >the range within is active I write:
> >
> > Dim rng1 As Range
> > Set rng1 = Selection
> >
> >So why, later when I reference this variable do I get that message?
>
> I wonder if you have a typo someplace.
>
> Do you have Option Explicit at the top of your macro?
>
> If you do not, put it there.
>
> I wonder if your two "rng1" variables are really the same. I ask because if
> you have not initialized a Range variable, the comment should say
> "rng1=Nothing" and not "rng1=Empty". "Empty" in your situation should refer to
> a Variant type variable that has not been initialized. That can happen if you
> have implicitly declared a variable rather than explicitly declaring it.
>
> =====================================
> Also, in your code, there is no reason (and it adds to my confusion :-))) to
> Select or Activate cells in order to do the kinds of operations you are doing.
> You've also go a lot of unnecessary stuff in there, that also makes debugging
> very difficult.
>
> For example, this code of yours:
> --------------------
> Range("A4").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(-2, 0).Range("A1").Select
> Selection.Copy
> --------------------
>
> does the same thing as this code (without Selecting cells):
> -------------------------------
> Range("A4").End(xlDown).Offset(-2, 0).Copy
> -------------------------------
>
> It may be that a lot of your code was generated by recording a macro. While
> that's good to get started, it does not generate the most efficient code, and
> makes debugging more difficult.
>
>
> --ron
>

[AG]

Well I found my problem.
I set rng1 in one sub of a module and called it later while in another sub
in the same module.
I didn’t realize that the reference would not transfer to the next sub. I
thought the reference was specific to the sheet.

Other than defining a name on the sheet for the group of cells I want to
reference, is there another way set the range that will transfer between
different subs?


"Ron Rosenfeld" wrote:

> On Thu, 10 Nov 2005 15:20:03 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >I have no doubt that the problem is with my coding.
> >This is a bit of an education for me.
> >
> >Using your suggestion
> >>set rng2 = rng1.Find("ACRNX2", LookIn:=xlValues, LookAt:=xlWhole)
> >
> >When I highlight rng1 I get a comment that rng1 is empty.
> >
> >Perhaps my initial approach is incorrect. While the sheet I want to define
> >the range within is active I write:
> >
> > Dim rng1 As Range
> > Set rng1 = Selection
> >
> >So why, later when I reference this variable do I get that message?
>
> I wonder if you have a typo someplace.
>
> Do you have Option Explicit at the top of your macro?
>
> If you do not, put it there.
>
> I wonder if your two "rng1" variables are really the same. I ask because if
> you have not initialized a Range variable, the comment should say
> "rng1=Nothing" and not "rng1=Empty". "Empty" in your situation should refer to
> a Variant type variable that has not been initialized. That can happen if you
> have implicitly declared a variable rather than explicitly declaring it.
>
> =====================================
> Also, in your code, there is no reason (and it adds to my confusion :-))) to
> Select or Activate cells in order to do the kinds of operations you are doing.
> You've also go a lot of unnecessary stuff in there, that also makes debugging
> very difficult.
>
> For example, this code of yours:
> --------------------
> Range("A4").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(-2, 0).Range("A1").Select
> Selection.Copy
> --------------------
>
> does the same thing as this code (without Selecting cells):
> -------------------------------
> Range("A4").End(xlDown).Offset(-2, 0).Copy
> -------------------------------
>
> It may be that a lot of your code was generated by recording a macro. While
> that's good to get started, it does not generate the most efficient code, and
> makes debugging more difficult.
>
>
> --ron
>

[Ron Rosenfeld]

On Fri, 11 Nov 2005 05:52:01 -0800, "AG" <AG@discussions.microsoft.com> wrote:

>Well I found my problem.
>I set rng1 in one sub of a module and called it later while in another sub
>in the same module.


Have you set OPTION EXPLICIT at the top of your subs as I said earlier? This
can be entered as the default and SHOULD be.

Had you done that, when you tried to compile the routines, you would have
gotten a "variable not defined" error at the time of compilation (and saved a
lot of time :-()

Before you go any further, in the VB Editor, select

Tools/Options/Editor and SELECT "Require Variable Declaration"


>I didn’t realize that the reference would not transfer to the next sub. I
>thought the reference was specific to the sheet.

It depends on whether you define it at the module level or at the procedure
level. See HELP for Dim.

>
>Other than defining a name on the sheet for the group of cells I want to
>reference, is there another way set the range that will transfer between
>different subs?
>

Sure, just Dim the variable at the module level.

e.g.

Option Explicit
Dim rng1 As Range

Sub foo()
Set rng1 = [A4]
FooBar
End Sub

Sub FooBar()
rng1.Clear
End Sub


You can also use the Public statement to have availability outside of the
module.


--ron

[AG]

Yes I did OPTION EXPLICIT at the top of the subs; good idea.
And your other thoughts worked too.
Thanks for all your help; I learned quite a lot.

"Ron Rosenfeld" wrote:

> On Fri, 11 Nov 2005 05:52:01 -0800, "AG" <AG@discussions.microsoft.com> wrote:
>
> >Well I found my problem.
> >I set rng1 in one sub of a module and called it later while in another sub
> >in the same module.
>
>
> Have you set OPTION EXPLICIT at the top of your subs as I said earlier? This
> can be entered as the default and SHOULD be.
>
> Had you done that, when you tried to compile the routines, you would have
> gotten a "variable not defined" error at the time of compilation (and saved a
> lot of time :-()
>
> Before you go any further, in the VB Editor, select
>
> Tools/Options/Editor and SELECT "Require Variable Declaration"
>
>
> >I didn’t realize that the reference would not transfer to the next sub. I
> >thought the reference was specific to the sheet.
>
> It depends on whether you define it at the module level or at the procedure
> level. See HELP for Dim.
>
> >
> >Other than defining a name on the sheet for the group of cells I want to
> >reference, is there another way set the range that will transfer between
> >different subs?
> >
>
> Sure, just Dim the variable at the module level.
>
> e.g.
>
> Option Explicit
> Dim rng1 As Range
>
> Sub foo()
> Set rng1 = [A4]
> FooBar
> End Sub
>
> Sub FooBar()
> rng1.Clear
> End Sub
>
>
> You can also use the Public statement to have availability outside of the
> module.
>
>
> --ron
>

[Ron Rosenfeld]

On Fri, 11 Nov 2005 07:39:10 -0800, "AG" <AG@discussions.microsoft.com> wrote:

>Yes I did OPTION EXPLICIT at the top of the subs; good idea.
>And your other thoughts worked too.
>Thanks for all your help; I learned quite a lot.

You're welcome.

You're on your way!
Post back with any more problems.


--ron