Naming a range

[jeff]

I posted this in a different form before. I appreciated the replies,
but they didn’t work for me. I will post this hoping that someone can
help me with this version of the problem.

ActiveSheet.Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select

‘What I’m looking for is to be able to name a range in the following
manner after the 2 ‘lines above.
‘The range name will be the word “Junk” plus the value of the
range name ‘TempNumber
‘EXAMPLE: if the value of TempNumber is “XXX”, then the new range
name
‘will be JunkXXX

Thanks for your help,
jeff

 

[Gary''s Student]

Here is a simple example using Z100 as the cell named TempNumber:

Sub marine()
Range("Z100").Name = "TempNumber"
Range("Z100").Value = "XXX"

ActiveSheet.Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select

Selection.Name = Range("TempNumber").Value & "Junk"
End Sub

 

[Rick Rothstein]

ActiveSheet.Range("A1").Select

ActiveCell.Offset(0, 1).Range("A1").Select

First off, the above two lines appear to select the cell immediately to the
right of the first specified range; that is, the first specified range is A1
and the cell that is ultimately selected is B1... I really don't know what
the intended purpose of that second Range("A1") is supposed to be doing. On
top of that, it is rarely necessary (and definitely not desirable) to select
a range of cells in order to operate on them. This single line of code
should what you have asked for...

ActiveSheet.Range("A1").Offset(0, 1).Name = TempNumber & "XXX"

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


I posted this in a different form before. I appreciated the replies,
but they didn’t work for me. I will post this hoping that someone can
help me with this version of the problem.

ActiveSheet.Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select

‘What I’m looking for is to be able to name a range in the following
manner after the 2 ‘lines above.
‘The range name will be the word “Junk” plus the value of the
range name ‘TempNumber
‘EXAMPLE: if the value of TempNumber is “XXX”, then the new range
name
‘will be JunkXXX

Thanks for your help,
jeff

 

[jeff]

First off, the above two lines appear to select the cell immediately to the
right of the first specified range; that is, the first specified range isA1
and the cell that is ultimately selected is B1... I really don't know what
the intended purpose of that second Range("A1") is supposed to be doing. On
top of that, it is rarely necessary (and definitely not desirable) to select
a range of cells in order to operate on them. This single line of code
should what you have asked for...

ActiveSheet.Range("A1").Offset(0, 1).Name = TempNumber & "XXX"

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

    Range("C2:C8193").Select         'select cells to export
    For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

    For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


I posted this in a different form before. I appreciated the replies,
but they didn’t work for me. I will post this hoping that someone can
help me with this version of the problem.

ActiveSheet.Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select

‘What I’m looking for is to be able to name a range in the following
manner after the 2 ‘lines above.
‘The range name will be the word      “Junk”     plus thevalue of the
range name  ‘TempNumber
‘EXAMPLE:  if the value of TempNumber is “XXX”, then the new range
name
‘will be     JunkXXX

Thanks for your help,
jeff

Thanks a bunch. I played with this and it works now. I DID have to
change something else. The value of TempNumber has a "#" in it, and I
guess you can't name a range with that symbol. Once I figured that
out, it all worked great.
Actually both solutions above work fine. I appreciate the effort.
Thanks again.
j.o.