Name Box

  • Thread starter Thread starter David Fisher
  • Start date Start date
D

David Fisher

Not sure if it is me or the program or what.

If I Ctrl and mouse highlight cols that will have a name, I then go
Insert>name>define and type in "MyTimeCols" in the name box. I click add
and exit.

When I exit, I go to name box and click on "MyTimeCols". It will
highlight the "MyTimeCols" only as far as AN #8 col which is wrong in
the first place since I highlighted cols up to col HG #65 col with
"MyTimeCols".

I have try using "MyTimeCols2" and "MyTimeCols3" for the cols that to be
part of a formula with no luck.

I have gone back and deleted name and resave the sheet and then do
everything over a gain.

Can someone tell me what I am doing wrong and how to fix the problem?

Thanks
 
Not sure if it is me or the program or what.

Neither am I. You say you Ctrl and mouse highlight *cols*, but then refer to
"AN #8 col" which sounds like a cell reference. Are you selecting whole
columns, or individual cells and/or groups of cells?

A name can contain up to 255 characters, so this may be a problem -- but if
so you ought to get an error message when you click Add.

It looks like you can create (apols to John W's "megaformulas") meganames by
combining names, eg: Name4 ; Refers to ; =Name2,Name3
but how effective this would be I can't say (tho' Google says Dave Peterson
said it's OK).

Or you could take out sheet refs, eg:
=!$A:$A,!$B:$B,!$C:$C,!$D:$D
which would probably make it refer to all sheets (?"global"?), but that
might not matter.

HTH,
Andy
 
Andy said:
Neither am I. You say you Ctrl and mouse highlight *cols*, but then refer to
"AN #8 col" which sounds like a cell reference. Are you selecting whole
columns, or individual cells and/or groups of cells?

A name can contain up to 255 characters, so this may be a problem -- but if
so you ought to get an error message when you click Add.

It looks like you can create (apols to John W's "megaformulas") meganames by
combining names, eg: Name4 ; Refers to ; =Name2,Name3
but how effective this would be I can't say (tho' Google says Dave Peterson
said it's OK).

Or you could take out sheet refs, eg:
=!$A:$A,!$B:$B,!$C:$C,!$D:$D
which would probably make it refer to all sheets (?"global"?), but that
might not matter.

HTH,
Andy
Click to expand...

I have highlighted 65 various whole cols up to col HG when I try naming
them "MyTimeCols"

After I have done this, I go to name box drop down list and click name
"MyTimeCols" which intern only highlights 8 cols up to col AN.

I have gone back and highlights the other cols that were supposed to
been name "MyTimeCols" in the first place and name them "MyTimeCols2"

I cannot see why all the 65 cols cannot have the same name spread over
130 cols in total.
 
As a test, I highlighted every other column starting with A. I stopped after
highlighting c.65 columns (lost count a bit). I defined this as a range, but
XL trunc'ed it to
=Sheet1!$A:$A,Sheet1!$C:$C,Sheet1!$E:$E,Sheet1!$G:$G,Sheet1!$I:$I,Sheet1!$K:
$K,Sheet1!$M:$M,Sheet1!$O:$O,Sheet1!$Q:$Q,Sheet1!$S:$S,Sheet1!$U:$U,Sheet1!$
W:$W,Sheet1!$Y:$Y,Sheet1!$AA:$AA,Sheet1!$AC:$AC,Sheet1!$AE:$AE,Sheet1!$AG:$A
G

AG is quite near to AN. This name is c.230 chars, which is quite close to
255.

Q1 - are any of your columns adjacent? If you're selecting them
individually, you could selecting them collectively by click'n'dragging on
the column headers (probably N/A, I think you want every other column).

Q2 - have you taken sheet references out of the name to free up space?

Q3 - throw me a bone here. What are you trying to achieve? There may be a
better way.

*There is a limit to how long the name can be!*

Rgds,
Andy
 
This might work for you. (try it against a test copy)

Insert|Name|Define
Delete the name myTimeCols

Now go back and select your columns. But instead of using insert|name|Define,
just type mytimecols into the namebox to the left of the formulabar.

(You can add names that way and you can select a range that way, but you can't
delete/modify a name using that name box.)

(Is it possible, you tried to change the existing mytimecols via the namebox?)

I think that Andy hit it on the head with the length limitation within the
insert|Name|Define dialog.

And if worse comes to worse, you could do it in code:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim cCtr As Long

With Worksheets("sheet1")
For cCtr = 1 To 130 Step 2
If myRng Is Nothing Then
Set myRng = .Columns(cCtr)
Else
Set myRng = Union(myRng, .Columns(cCtr))
End If
Next cCtr
myRng.Name = "myTimeCols"
'just for testing
Application.Goto .Range("mytimecols"), scroll:=True
End With
End Sub

Maybe in your auto_open/workbook_open so the name gets "reset" each time you
open the workbook. (Or you could add it to your "run once as a developer"
module (if it never changes by the user.))
 
Dave said:
This might work for you. (try it against a test copy)

Insert|Name|Define
Delete the name myTimeCols

Now go back and select your columns. But instead of using insert|name|Define,
just type mytimecols into the namebox to the left of the formulabar.
Click to expand...

I did this first before doing define with no luck.
(You can add names that way and you can select a range that way, but you can't
delete/modify a name using that name box.)

(Is it possible, you tried to change the existing mytimecols via the namebox?)

I think that Andy hit it on the head with the length limitation within the
insert|Name|Define dialog.
Click to expand...

Little lose here as to limit. Are you saying that each figure can only
add up to 255? Sheet1!$a:$a,sheet1!$d:$d

What is the limit not using define?

If that is the case, then if adding "MyTimeCols2", "MyTimeCols3" should
get around this problem.
And if worse comes to worse, you could do it in code:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim cCtr As Long

With Worksheets("sheet1")
For cCtr = 1 To 130 Step 2
If myRng Is Nothing Then
Set myRng = .Columns(cCtr)
Else
Set myRng = Union(myRng, .Columns(cCtr))
End If
Next cCtr
myRng.Name = "myTimeCols"
'just for testing
Application.Goto .Range("mytimecols"), scroll:=True
End With
End Sub

Maybe in your auto_open/workbook_open so the name gets "reset" each time you
open the workbook. (Or you could add it to your "run once as a developer"
module (if it never changes by the user.))


Dave Peterson
(e-mail address removed)
Click to expand...

It will never change by user.
<snip>
Dave
 
There are only 4 case where 2 or more cols are side by side.
How do you do that as I never done this before? Do I just delete all
sheet 1? =Sheet1!$A:$A,!$D:$D,!$E:$E,!$K:$K,

If so, I will need 5 figures per col. If that is the case, then 65 cols
time 5 = 325 charters and that is before I add more cols to the templet
in the workbooks.
Click to expand...

If you "delete all sheet 1" (manually edit in the Define Name dialog), XL
will like as not put it back in when you click OK. If you're including
entire columns only, you *ought to be able* to dispense with and $s. Thus
you can handle 5 columns with 10 characters, viz -

=!A:D,!F:F

Taking out any sheet references will make it apply to any sheet (although a
purist might say, stop it applying only to the sheet it's supposed to). I
still think your best bet would be to do 2-3 "sub-names", then bolt them
together with -

RangeAll = Range1,Range2,Range3

Rgds,
Andy
 
Andy said:
If you "delete all sheet 1" (manually edit in the Define Name dialog), XL
will like as not put it back in when you click OK. If you're including
entire columns only, you *ought to be able* to dispense with and $s. Thus
you can handle 5 columns with 10 characters, viz -

=!A:D,!F:F

Taking out any sheet references will make it apply to any sheet (although a
purist might say, stop it applying only to the sheet it's supposed to). I
still think your best bet would be to do 2-3 "sub-names", then bolt them
together with -

RangeAll = Range1,Range2,Range3

Rgds,
Andy
Click to expand...

On Error GoTo EndMacro
If Application.Intersect(Target, RangeAll = MyTimeCols,
MyTimeCols2, MyTimeCols3, MyTimeCols4) Is Nothing Then
Exit Sub

I get 0:00:00 in cels when I type in 1234. The year date gets change to
5/18/1903. The cell is suppose to show 12:34:00

Have cut the numbers of down to about 15 cols each.

Did not do =!a:a,!d:d,

Any reasons for date not to work?
 
Dave said:
It's not every other column?

put the columns in this:

Option Explicit
Sub testme01()
worksheets("sheet1").range("a:a,c:c,D:h,l:q,r:r,t:t").name = "Mytimecols"
End Sub

If the columns are by themselves, do like a:a or c:c. If they're grouped, do
like d:h.

Then run this macro once.
Click to expand...

Macro works fine.
====
And you really deleted the original range name mytimecols before using the
namebox and it didn't work? I don't think I've seen it fail (except when the
range already existed).
Click to expand...

Yes I did delete all reference to range.

I think it was Excel 2003 that was corrupt. I end up shutting it down
and restart it. Could had been something in memory that was causing the
problem. (shaking head)

I end up making a fresh templet from scratch and highlight only 15 cols
at a time and try each MyTimeCols, MyTimeCols2 MyTimeCols3, MyTimeCols4
one at a time to make sure it work first.

I then add them all together and it work fine now after how may tries?
sheeeeeee!!!!!

In the end it work like it was suppose to weeks ago.

Thanks
 
Andy said:
If you "delete all sheet 1" (manually edit in the Define Name dialog), XL
will like as not put it back in when you click OK. If you're including
entire columns only, you *ought to be able* to dispense with and $s. Thus
you can handle 5 columns with 10 characters, viz -

=!A:D,!F:F

Taking out any sheet references will make it apply to any sheet (although a
purist might say, stop it applying only to the sheet it's supposed to). I
still think your best bet would be to do 2-3 "sub-names", then bolt them
together with -

RangeAll = Range1,Range2,Range3

Rgds,
Andy
Click to expand...

Thanks for the help.

Finally got to work in a new fresh workbook as a 4 different unites. Use
only 15 cols per number.Range(Range1,Range2,Range3,range4)

Will remember the 255 number.
 
drum118 said:
Yes I did delete all reference to range.
Click to expand...

No offense intended, but sometimes it's easy to mix up which worked and which
didn't.

I think it was Excel 2003 that was corrupt. I end up shutting it down
and restart it. Could had been something in memory that was causing the
problem. (shaking head)
Click to expand...

I haven't seen xl2003. Maybe you should report back to the beta tester group to
see if anyone else has had the problem.
I then add them all together and it work fine now after how may tries?
sheeeeeee!!!!!
Click to expand...

Is that a happy sheeeeeeee!!!!!?
 
Dave said:
No offense intended, but sometimes it's easy to mix up which worked and which
didn't.
Click to expand...

Very true. I did a few things that I did not know before regarding
limitation. Know now to test as I go on something like this again.

I haven't seen xl2003. Maybe you should report back to the beta tester group to
see if anyone else has had the problem.


Is that a happy sheeeeeeee!!!!!?
Click to expand...

Yessssssss!!!!!!!!
 
Back
Top