Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
On Feb 12, 7:04*pm, Dave Peterson wrote:
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:=t rue) end with And I'm not sure what that "intRow + ",0)", either. wrote: 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 = generalconfusion. My data sheet contains about 25 columns and 50 rows of data. *I've created a macro to define anamedrange for each columnusingthe 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 (namedafter hull number). *On the sheet for the individual ship, I'm trying to set formulas for updating the event dates. *This works wellusingthe 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 mynamedranges) 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 mynamedrangesto 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- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
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? wrote: <<snipped 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
On Feb 13, 12:36*pm, Dave Peterson wrote:
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? wrote: <<snipped Dave, Thanks for the quick reply. *I'm basically trying to replace a hard column reference "G" with the column property of anamedrange. "FullNameValue" is one of thenamedrangescreated by the macro that defines anamedrange for each column. *Part of the problem may be that thenamedrangesare defined on the first sheet "Date Summary", and I'm trying to use them to set values on other sheets. *They are workbook levelnamedranges. 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- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
ActiveCell.offset(1,0).Formula _
= "=" & worksheets("date summary").Cells(intRw, _ activesheet.Range("FullNameValue").column).Address (external:=True) I think. wrote: <<snipped - 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
On Feb 13, 1:44*pm, Dave Peterson wrote:
ActiveCell.offset(1,0).Formula _ * * = "=" & worksheets("date summary").Cells(intRw, _ * * * * * activesheet.Range("FullNameValue").column).Address (external:=True) I think. wrote: <<snipped - 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 rangenamed"FullNameValue". *It's RefersTo value shows as ="$W:$W". *It is a Workbook levelnamedrange, 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- 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
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...) wrote: On Feb 13, 1:44 pm, Dave Peterson wrote: ActiveCell.offset(1,0).Formula _ = "=" & worksheets("date summary").Cells(intRw, _ activesheet.Range("FullNameValue").column).Address (external:=True) I think. wrote: <<snipped - 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 rangenamed"FullNameValue". It's RefersTo value shows as ="$W:$W". It is a Workbook levelnamedrange, 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- 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
On Feb 13, 4:29*pm, Dave Peterson wrote:
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...) wrote: On Feb 13, 1:44 pm, Dave Peterson wrote: ActiveCell.offset(1,0).Formula _ * * = "=" & worksheets("date summary").Cells(intRw, _ * * * * * activesheet.Range("FullNameValue").column).Address (external:=True) I think. wrote: <<snipped - 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 rangenamed"FullNameValue". *It's RefersTo value shows as ="$W:$W". *It is a Workbook levelnamedrange, 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- 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 mynamedranges. Reviewing myrangesvia 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 othernamedrangesdisplays ="$W: $W" *(with correct letters for each column). *If I delete and manually create thenamedrangesfor column W, the 'Refers to' value changes from * ="$W:$W" * to * ='DATE SUMMARY'!$W$W. *I need the program to resetnamedrangeseach time the user leaves the "Date Summary" sheet to ensure correct columns are used. *How can I change the macro that creates thenamedrangesto ensure the Refers to format matches the one provided by manually definingranges? Optimism has returned! *Thank you for sharing your knowledge! v/r, hglembin -- 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.Col umn) 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
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. wrote: <<snipped 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.Col umn) 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
ps. replace was added in xl2k.
If you're using xl97, you can use: mystr = application.substitute(mystr, chr(34),"_") Dave Peterson wrote: 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. wrote: <<snipped 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.Col umn) 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 -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confusion Using Named Ranges
On Feb 16, 10:21*am, Dave Peterson wrote:
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. wrote: <<snipped I'm very close to having this work as needed. If I can edit the code that creates eachnamedrange 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 thenamedrange correctly without the double quotes. " I cannot find any additional information on exactly what that code is. This is the code I'm currentlyusingto create mynamedranges. 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.Col umn) * * 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- Hide quoted text - - Show quoted text - BINGO! Many thanks for your patience and willingness to help. v/r, hglembin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges and pasting formulas with named references | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |