Confusion Using Named Ranges

[hglembin]

Hi All,

I was sure this problem would be in the newsgroups already, but 2 days
of searching and trying various things that seemed applicable =
general confusion.

My data sheet contains about 25 columns and 50 rows of data. I've
created a macro to define a named range for each column using the
header row values for range names. That seems to work well.

The first column contains hull numbers for ships and the rest of the
columns contain dates for various events. Each hull number relates to
a separate sheet that contains additional information about the boat.
I need to be able to update the individual sheets after the main "Date
Summary" sheet is sorted. Users will be able to select any event
column and sort all boats by the dates listed.

When a sheet other than "Date Summary" is activated, i set a variable
"intRw" to the current row on "Date Summary" that contains the date
for that particular sheet (named after hull number). On the sheet for
the individual ship, I'm trying to set formulas for updating the event
dates. This works well using the following line and a "hard"
reference to the column number:

ActiveCell.offset(1,0).Formula = "="+strDS + "G" + strRw
(where strDS="'" + "DATE SUMMARY" + "'" + "!", "G" is column G, and
strRw is string equiv to intRow ref)

I'm trying to eliminate the hard column refs just in case anyone
inserts/deletes a column or two.

Trying to use something like:

ActiveCell.offset(1,0).Formula = "=Range('FullNameValue')"+ "(" +
intRow + ",0)"
(where "FullNameValue" is one of my named ranges)

This line is giving me a "Type mismatch" error. I've also tried to
use a "Cells(x,y)" formula without luck.

I think I am not understanding how to correctly use my named ranges to
identify the correct column. When I tried to use Range
("FullNameValue").Column, I received a "Method 'Range' of object
_global failed" error.

Any guidance would be greatly appreciated.

Respectfully,
hglembin

 

[Dave Peterson]

I don't understand what you're doing, but this may help get the syntax correct:

with worksheets("Date summary")
ActiveCell.offset(1,0).Formula _
= "=" & .cells(introw, _
.range("FullNameValue").column).address(external:=true)
end with

But I'm confused about what FullNameValue really is. If it's a string variable,
then don't include the double quotes:

with worksheets("Date summary")
ActiveCell.offset(1,0).Formula _
= "=" & .cells(introw, _
.range(FullNameValue).column).address(external:=true)
end with

And I'm not sure what that "intRow + ",0)", either.

 

[hglembin]

I don't understand what you're doing, but this may help get the syntax correct:

with worksheets("Date summary")
  ActiveCell.offset(1,0).Formula _
    = "=" & .cells(introw, _
               .range("FullNameValue").column).address(external:=true)
end with

But I'm confused about what FullNameValue really is.  If it's a string variable,
then don't include the double quotes:

with worksheets("Date summary")
  ActiveCell.offset(1,0).Formula _
    = "=" & .cells(introw, _
               .range(FullNameValue).column).address(external:=true)
end with

And I'm not sure what that "intRow + ",0)", either.



















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks for the quick reply. I'm basically trying to replace a hard
column reference "G" with the column property of a named range.
"FullNameValue" is one of the named ranges created by the macro that
defines a named range for each column. Part of the problem may be
that the named ranges are defined on the first sheet "Date Summary",
and I'm trying to use them to set values on other sheets. They are
workbook level named ranges.

For example: when the user selects sheet "888" (which matches a ship's
hull number), I need to reset values for for 20 cells on the selected
sheet. The values to be set are on the "Date Summary" sheet.

Referencing your suggested syntax, while adjusting for the fact that
the cell I'm setting the formula for is on sheet "888" (not "Date
Summary"), I tried:


With ActiveSheet

ActiveCell.offset(1,0).Formula = "=" + .Cells(intRw, .Range
(FullNameValue).column).Address(external:=True)

end with

this test resulted in a 1004 run time error (Application-defined or
object-defined error)

Note: my reason for getting away from the hard column references is to
allow small changes to column names and insert/delete of columns. In
addition, I'm working around the problem of references not updating
after a data set is sorted.

Thanks again,
hglembin

 

[Dave Peterson]

How about this:

What's the name of the sheet getting the formula.
What's should the formula look like (if you typed it manually).
What is FullNameRange? Is it a variable or just a string?
What is the name of the sheet that owns FullNameRange?

 

[hglembin]

How about this:

What's the name of the sheet getting the formula.
What's should the formula look like (if you typed it manually).
What is FullNameRange?  Is it a variable or just a string?
What is the name of the sheet that owns FullNameRange?







(e-mail address removed) wrote:












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Sorry this is so confusing.

1. The name of the sheet getting the formula is "888"

2. If I placed the formula on the sheet it would be "='Date Summary'!
W43
(because info for hull number 888 is on row 43 of sheet Date Summary,
and column 'W' contains 'FullName' info.)

3 & 4. I have a column with the header "FullNameValue" which is a
concatenation
of the ship name, type, and hull number. I use a macro that defines a
named
range for each column, and uses the column header as the range name.
So, I
also have a range named "FullNameValue". It's RefersTo value shows as
="$W:$W". It is a Workbook level named range, so it is not 'local' to
any one
sheet.


Note: my code worked great when i had hard column references: i.e. -
ActiveCell.offset(1,0).Formula = "="+strDS + "W" + strRw

where strDS="'" + "DATE SUMMARY" + "'" + "!" (sheet reference),
"W" is column W which contains "FullNameValue" data,
and strRw which is a string representing the row the ships info is on.

Thanks again for your time,
hglembin

 

[Dave Peterson]

ActiveCell.offset(1,0).Formula _
= "=" & worksheets("date summary").Cells(intRw, _
activesheet.Range("FullNameValue").column).Address(external:=True)

I think.

 

[hglembin]

ActiveCell.offset(1,0).Formula _
    = "=" & worksheets("date summary").Cells(intRw, _
          activesheet.Range("FullNameValue").column).Address(external:=True)

I think.

(e-mail address removed) wrote:

<<snipped>>













--

Dave Peterson- Hide quoted text -

- Show quoted text -

Getting There!!

I used your previous recommendation (minus the "activesheet" prior to
"Range" since range is on the Date Summary sheet) and I've been able
to get it to work for one of my named ranges.

Reviewing my ranges via the "Define Name" dialog box, I noticed that
the range for which the formula works is different. The "Refers to"
value for that range is "='DATE SUMMARY'!$S$S".
The refers to value for all other named ranges displays ="$W:
$W" (with correct letters for each column). If I delete and manually
create the named ranges for column W, the 'Refers to' value
changes from ="$W:$W" to ='DATE SUMMARY'!$W$W. I need the
program to reset named ranges each time the user leaves the "Date
Summary" sheet to ensure correct columns
are used. How can I change the macro that creates the named ranges to
ensure the Refers to format matches the one provided by manually
defining ranges?

Optimism has returned! Thank you for sharing your knowledge!

v/r,
hglembin

 

[Dave Peterson]

if the worksheet that has the correct column for that range is 'date summary',
you can use:

ActiveCell.offset(1,0).Formula _
= "=" & worksheets("date summary").Cells(intRw, _
worksheets("date summary") _
.Range("FullNameValue").column).Address(external:=True)

(I think...)

 

[hglembin]

if the worksheet that has the correct column for that range is 'date summary',
you can use:

ActiveCell.offset(1,0).Formula _
  = "=" & worksheets("date summary").Cells(intRw, _
            worksheets("date summary") _
             .Range("FullNameValue").column).Address(external:=True)

(I think...)












--

Dave Peterson- Hide quoted text -

- Show quoted text -

I'm very close to having this work as needed.

If I can edit the code that creates each named range so there are no
double quotes, all else works.
I found a post by OssieMac (Feb 5th) that states:

"The code that I have given you will create the named range correctly
without
the double quotes. "

I cannot find any additional information on exactly what that code is.

This is the code I'm currently using to create my named ranges.
myHdrArray is an array that contains
each column header in order.

Dim intHdrEnd As Integer

'NAME A RANGE THAT REFERS TO EACH COLUMN BY HEADER NAME
Range("a1").Select
Selection.End(xlToRight).Select
intHdrEnd = ActiveCell.Column
Range("a1").Select
Dim shtMain As String
shtMain = "DATE SUMMARY"
For N = 1 To intHdrEnd
ActiveWorkbook.Names.Add Name:=CStr(myHdrArray(1, N)),
RefersTo:=Worksheets(shtMain).Range(ActiveCell.Column)
ActiveCell.Offset(0, 1).Select
If N = intHdrEnd Then Exit Sub
Next N

Any help correctly creating these defined names would be GREATLY
appreciated.

Thank you once again. Without the help, I would be unable to get this
working.
hglembin

 

[Dave Peterson]

Dim myStr as string

For N = 1 To intHdrEnd
mystr = myHdrArray(1, N)
mystr = replace(mystr, chr(34),"_")
mystr = replace(mystr, " ", "_")
'then use mystr as the name
activecell.entirecolumn.name = mystr

There are other characters/strings that aren't legal for names, either.

 

[Dave Peterson]

ps. replace was added in xl2k.

If you're using xl97, you can use:

mystr = application.substitute(mystr, chr(34),"_")

 

[hglembin]

Dim myStr as string

For N = 1 To intHdrEnd
  mystr = myHdrArray(1, N)
  mystr = replace(mystr, chr(34),"_")
  mystr = replace(mystr, " ", "_")
  'then use mystr as the name
  activecell.entirecolumn.name = mystr

There are other characters/strings that aren't legal for names, either.

(e-mail address removed) wrote:

<<snipped>>
















--

Dave Peterson- Hide quoted text -

- Show quoted text -

BINGO! Many thanks for your patience and willingness to help.

v/r,
hglembin