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 doesnt seem to work.

Hi AG,

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

Try:

Set rng1 = Selection

---
Regards,
Norman



"AG" wrote in message
...
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.

On Thu, 10 Nov 2005 08:25:06 -0800, "AG" 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

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" 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 doesnt seem to work.

rng1.Select

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




--ron

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" 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 doesnt seem to work.

rng1.Select

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




--ron

On Thu, 10 Nov 2005 12:38:09 -0800, "AG" 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

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, heres 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" 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

On Thu, 10 Nov 2005 15:20:03 -0800, "AG" 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

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.

Ill get back to you on the issue at hand.


"Ron Rosenfeld" wrote:

On Thu, 10 Nov 2005 15:20:03 -0800, "AG" 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

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 didnt 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" 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

On Fri, 11 Nov 2005 05:52:01 -0800, "AG" 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

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" 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 didnt 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

On Fri, 11 Nov 2005 07:39:10 -0800, "AG" 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