How to define and select a range

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.

Hi AG,

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

Try:

Set rng1 = Selection

---
Regards,
Norman



"AG" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> 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" <[email protected]> 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
>

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" <[email protected]> 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
>

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