Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _
RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
Try it this way...
shtSup1.Names.Add Name:="Sup2"_BCAPA_Ratings", RefersTo:=Range( _ "Supplier!$C$6,Supplier!$C$16,Supplier!$C$23,Suppl ier!$C$36,Supplier!$C$56"), _ Visible:=True Note that the Range function is used to create the RefersTo reference and that its argument is a String value (hence, the quote marks). -- Rick (MVP - Excel) "Walter" wrote in message ... shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
Maybe something like
Sub BBB() Dim shtSup1 As Worksheet, Rng As Range Set shtSup1 = Sheets("Supplier") Set Rng = Union(shtSup1.Range("C6"), shtSup1.Range("C16"), _ shtSup1.Range("C23"), shtSup1.Range("C36"), _ shtSup1.Range("C56")) shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", RefersTo:=Rng, Visible:=True Application.Goto Reference:="Sup1_BCAPA_Ratings" End Sub Hope this helps, Hutch "Walter" wrote: shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
Dim shtSup1 As Worksheet
Dim rng As Range Set shtSup1 = Sheets("Supplier") Set rng = shtSup1.Range("C6,C16,C23,C36,C56") rng.Name = "Sup1_BCAPA_Ratings" -- __________________________________ HTH Bob "Walter" wrote in message ... shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
Just to add to Bob's response...
If you wanted to use a worksheet level name: Set shtSup1 = Sheets("Supplier") with shtSup1 Set rng = .Range("C6,C16,C23,C36,C56") rng.Name = "'" & .name & "'!Sup1_BCAPA_Ratings" end with Bob Phillips wrote: Dim shtSup1 As Worksheet Dim rng As Range Set shtSup1 = Sheets("Supplier") Set rng = shtSup1.Range("C6,C16,C23,C36,C56") rng.Name = "Sup1_BCAPA_Ratings" -- __________________________________ HTH Bob "Walter" wrote in message ... shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
Can I request the name of the tab in this formula instead of putting the word
Supplier in it: NameOfTab!$C$6 What would I enter in place of "NameOfTab" to have the range automatically include the tab name in the formula for all cell references? "Rick Rothstein" wrote: Try it this way... shtSup1.Names.Add Name:="Sup2"_BCAPA_Ratings", RefersTo:=Range( _ "Supplier!$C$6,Supplier!$C$16,Supplier!$C$23,Suppl ier!$C$36,Supplier!$C$56"), _ Visible:=True Note that the Range function is used to create the RefersTo reference and that its argument is a String value (hence, the quote marks). -- Rick (MVP - Excel) "Walter" wrote in message ... shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
I am having problems with the merged cells values writting out to a specified destination. Here is my code below. Sub Import() Dim shtSup1 As Worksheet Dim field1 As Range, field2 As Range, field3 As Range, field4 As Range, Rng As Range Set field1 = Range("F44:F53") Set field2 = Range("H44:H53") Set field3 = Range("D44:D53") Set field4 = Range("B44:B53") Set Rng = Union(field1, field2, field3, field4) Rng.Copy Destination:=shtSup1.Range("S43") End Sub Please review and let me know what I am doing wrong. Thank you- "Tom Hutchins" wrote: Maybe something like Sub BBB() Dim shtSup1 As Worksheet, Rng As Range Set shtSup1 = Sheets("Supplier") Set Rng = Union(shtSup1.Range("C6"), shtSup1.Range("C16"), _ shtSup1.Range("C23"), shtSup1.Range("C36"), _ shtSup1.Range("C56")) shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", RefersTo:=Rng, Visible:=True Application.Goto Reference:="Sup1_BCAPA_Ratings" End Sub Hope this helps, Hutch "Walter" wrote: shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges: Can I do This? How?
It doesn't look like shtSup1 has been assigned to a worksheet. In passing, as your ranges are all exactly the same size you might, with luck, end up pasting all four areas. Normally though you'ld only end up pasting the 1st area. Typically you'll need to do something like this dim rngDest as Range, ar as Range Set shtSup1 = ActiveSheet Set rngDest = shtSup1.Range("S43") For Each ar In Rng.Areas ar.Copy rngDest Set rngDest = rngDest.Offset(, ar.Columns.Count) Next Regards, Peter T "Neecy" wrote in message ... I am having problems with the merged cells values writting out to a specified destination. Here is my code below. Sub Import() Dim shtSup1 As Worksheet Dim field1 As Range, field2 As Range, field3 As Range, field4 As Range, Rng As Range Set field1 = Range("F44:F53") Set field2 = Range("H44:H53") Set field3 = Range("D44:D53") Set field4 = Range("B44:B53") Set Rng = Union(field1, field2, field3, field4) Rng.Copy Destination:=shtSup1.Range("S43") End Sub Please review and let me know what I am doing wrong. Thank you- "Tom Hutchins" wrote: Maybe something like Sub BBB() Dim shtSup1 As Worksheet, Rng As Range Set shtSup1 = Sheets("Supplier") Set Rng = Union(shtSup1.Range("C6"), shtSup1.Range("C16"), _ shtSup1.Range("C23"), shtSup1.Range("C36"), _ shtSup1.Range("C56")) shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", RefersTo:=Rng, Visible:=True Application.Goto Reference:="Sup1_BCAPA_Ratings" End Sub Hope this helps, Hutch "Walter" wrote: shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _ RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _ Supplier!$C$56,Visible:=True I want to add named ranges in my workbook with VBA. However, the range that I have is discontiguous as noted above. I can enter it in the Insert/Name/Define and it works. How do I write the code so that it compiles in VBA? I keep getting an error. Supplier is the name of the worksheet. I am working in Excel 2003. Thank you! |
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) |