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