Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
Hello,
Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
WOWEE!!!!!
|:0 name several smaller regions....... jpgcells1 jpgcells2 jpgcells3 it may mean a slightly longer formula somewhere, but that should help........ :) susan On Mar 28, 7:31 am, Longbow wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdexhttp://www.developersdex.com*** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
Hi Chris,
Manually or programmatically with a string address, defining or editing a Name is limited to 255 characters. In practice can be less than that. In code you can define the name to that refer directly to a range. The limit is between 149 and 224 multiple areas depending on the combination of single or multiple cells in each area. The limit is not related to length of address. Sub test() Dim rng As Range Dim i As Long Set rng = Range("A1:B1") For i = 3 To 298 Step 2 Set rng = Union(rng, Range(Cells(i, 1), Cells(i, 2))) Next ActiveWorkbook.Names.Add "LongName", rng Set rng = Nothing Set rng = Range("LongName") rng.Select MsgBox rng.Count & " cells" & vbCr & _ rng.Areas.Count & " areas" '149 End Sub There's no way to manually edit a name with that many areas. However with fewer, it may help to temporarily rename the sheet to a single letter. There are other ways (lot of code) to define a name with much more than the 149/224 limit, though I limit to 3,600 areas for practical reasons. Regards, Peter T "Longbow" wrote in message ... Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
And dont forget that you can nest names ;
jpgCells defined as jpgCells1,jpgCells2 ..... Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Susan" wrote in message ups.com... WOWEE!!!!! |:0 name several smaller regions....... jpgcells1 jpgcells2 jpgcells3 it may mean a slightly longer formula somewhere, but that should help........ :) susan On Mar 28, 7:31 am, Longbow wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdexhttp://www.developersdex.com*** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
Thanks Susan, Peter T and Charles Williams. In column B of my
spreadsheet, I need to name a range of cells called: jpgCells. This occurs every 21 rows for 200 instances (except the very first instance). This will enable a user to double-click on these cells to insert a jpg picture. Can I name each of these cells in a different way to achieve my outcome? More help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
Sub MakeName()
Dim s As String, rng As Range s = "B2,B21,B42,B63,B84,B105,B126,B147,B168,B189," & _ "B210,B231,B252,B273,B294,B315,B336,B357,B378, " & _ "B399,B420,B441,B462,B483,B504,B525,B546,B567, " & _ "B588,B609,B630,B651,B672,B693,B714,B735,B756, " & _ "B777,B798,B819,B840,B861,B882,B903,B924,B945, " & _ "B966,B987,B1008,B1029,B1050,B1071,B1092,B1113 ," & _ "B1134,B1155,B1176,B1197,B1218,B1239,B1260,B12 81," & _ "B1302,B1323,B1344,B1365,B1386,B1407,B1428,B14 49," & _ "B1470,B1491,B1512,B1533,B1554,B1575,B1596,B17 22," & _ "B1743,B1764,B1785,B1806,B1806,B1827,B1848,B18 69," & _ "B1890,B1911,B1932,B1953,B1974,B1995,B2016,B20 37," & _ "B2058,B2079,B2100,B2121,B2142,B2163,B2184,B22 05," & _ "B2226,B2247,B2268,B2289,B2310,B2331,B2352,B23 73," & _ "B2394,B2415,B2436,B2457,B2478,B2499,B2520,B25 41," & _ "B2562,B2583,B2604,B2625,B2646,B2667,B2688,B27 09," & _ "B2730,B2751,B2772,B2793,B2814,B2835,B2856,B28 77," & _ "B2898,B2919,B2940,B2961,B2982,B3003,B3024,B30 45," & _ "B3066,B3087,B3108,B3129,B3150,B3171,B3192,B32 13," & _ "B3234,B3255,B3276,B3297,B3318,B3339,B3360,B33 81," & _ "B3402,B3423,B3444,B3465,B3486,B3507,B3528,B35 49," & _ "B3570,B3591,B3612,B3633,B3654,B3675,B3696,B37 17," & _ "B3738,B3759,B3780,B3801,B3822,B3843,B3864,B38 85," & _ "B3906,B3927,B3948,B3969,B3990,B4011,B4032,B40 53," & _ "B4074, B4095, B4116, B4137, B4156, B4179, B4220" v = Split(s, ",") With Worksheets("Items") Set rng = .Range(v(LBound(v))) For i = LBound(v) + 1 To UBound(v) Set rng = Union(rng, .Range(v(i))) Next End With rng.Name = "jpgCells" End Sub Don't try to edit it manually after it is created or it will screw it up. -- Regards, Tom Ogilvy "Longbow" wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
One way:
Dim rTemp As Range Dim i As Long Set rTemp = Union(Range("B2"), Range("B4220")) For i = 21 to 4179 Step 21 Set rTemp = Union(rTemp, Cells(i, 2)) Next i rTemp.Name = "jpgCells" In article , Longbow wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
I tested that approach myself, but it didn't appear that there is a constant
21 cell interval. -- Regards, Tom Ogilvy "JE McGimpsey" wrote: One way: Dim rTemp As Range Dim i As Long Set rTemp = Union(Range("B2"), Range("B4220")) For i = 21 to 4179 Step 21 Set rTemp = Union(rTemp, Cells(i, 2)) Next i rTemp.Name = "jpgCells" In article , Longbow wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
I stand corrected. His later post says every 21 is what he wants.
I guess he had some typos: -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: I tested that approach myself, but it didn't appear that there is a constant 21 cell interval. -- Regards, Tom Ogilvy "JE McGimpsey" wrote: One way: Dim rTemp As Range Dim i As Long Set rTemp = Union(Range("B2"), Range("B4220")) For i = 21 to 4179 Step 21 Set rTemp = Union(rTemp, Cells(i, 2)) Next i rTemp.Name = "jpgCells" In article , Longbow wrote: Hello, Could some one please help me with the following? From the tool menu - when I choose insert name define and enter the following criteria: Names in Workbook = jpgCells Refers to: ITEMS!$B$2,ITEMS!$B$21,ITEMS!$B$42,ITEMS!$B$63,ITE MS!$B$84,ITEMS!$B$105, ITEMS!$B$126,ITEMS!$B$147,ITEMS!$B$168,ITEMS!$B$18 9,ITEMS!$B$210,ITEMS!$ B$231,ITEMS!$B$252,ITEMS!$B$273,ITEMS!$B$294,ITEMS !$B$315,ITEMS!$B$336,I TEMS!$B$357,ITEMS!$B$378,ITEMS!$B$399,ITEMS!$B$420 ,ITEMS!$B$441,ITEMS!$B $462,ITEMS!$B$483,ITEMS!$B$504,ITEMS!$B$525,ITEMS! $B$546,ITEMS!$B$567,IT EMS!$B$588,ITEMS!$B$609,ITEMS!$B$630,ITEMS!$B$651, ITEMS!$B$672,ITEMS!$B$ 693,ITEMS!$B$714,ITEMS!$B$735,ITEMS!$B$756,ITEMS!$ B$777,ITEMS!$B$798,ITE MS!$B$819,ITEMS!$B$840,ITEMS!$B$861,ITEMS!$B$882,I TEMS!$B$903,ITEMS!$B$9 24,ITEMS!$B$945,ITEMS!$B$966,ITEMS!$B$987,ITEMS!$B $1008,ITEMS!$B$1029,IT EMS!$B$1050,ITEMS!$B$1071,ITEMS!$B$1092,ITEMS!$B$1 113,ITEMS!$B$1134,ITEM S!$B$1155,ITEMS!$B$1176,ITEMS!$B$1197,ITEMS!$B$121 8,ITEMS!$B$1239,ITEMS! $B$1260,ITEMS!$B$1281,ITEMS!$B$1302,ITEMS!$B$1323, ITEMS!$B$1344,ITEMS!$B $1365,ITEMS!$B$1386,ITEMS!$B$1407,ITEMS!$B$1428,IT EMS!$B$1449,ITEMS!$B$1 470,ITEMS!$B$1491,ITEMS!$B$1512,ITEMS!$B$1533,ITEM S!$B$1554,ITEMS!$B$157 5,ITEMS!$B$1596,ITEMS!$B$1722,ITEMS!$B$1743,ITEMS! $B$1764,ITEMS!$B$1785, ITEMS!$B$1806,ITEMS!$B$1806,ITEMS!$B$1827,ITEMS!$B $1848,ITEMS!$B$1869,IT EMS!$B$1890,ITEMS!$B$1911,ITEMS!$B$1932,ITEMS!$B$1 953,ITEMS!$B$1974,ITEM S!$B$1995,ITEMS!$B$2016,ITEMS!$B$2037,ITEMS!$B$205 8,ITEMS!$B$2079,ITEMS! $B$2100,ITEMS!$B$2121,ITEMS!$B$2142,ITEMS!$B$2163, ITEMS!$B$2184,ITEMS!$B $2205,ITEMS!$B$2226,ITEMS!$B$2247,ITEMS!$B$2268,IT EMS!$B$2289,ITEMS!$B$2 310,ITEMS!$B$2331,ITEMS!$B$2352,ITEMS!$B$2373,ITEM S!$B$2394,ITEMS!$B$241 5,ITEMS!$B$2436,ITEMS!$B$2457,ITEMS!$B$2478,ITEMS! $B$2499,ITEMS!$B$2520, ITEMS!$B$2541,ITEMS!$B$2562,ITEMS!$B$2583,ITEMS!$B $2604,ITEMS!$B$2625,IT EMS!$B$2646,ITEMS!$B$2667,ITEMS!$B$2688,ITEMS!$B$2 709,ITEMS!$B$2730,ITEM S!$B$2751,ITEMS!$B$2772,ITEMS!$B$2793,ITEMS!$B$281 4,ITEMS!$B$2835,ITEMS! $B$2856,ITEMS!$B$2877,ITEMS!$B$2898,ITEMS!$B$2919, ITEMS!$B$2940,ITEMS!$B $2961,ITEMS!$B$2982,ITEMS!$B$3003,ITEMS!$B$3024,IT EMS!$B$3045,ITEMS!$B$3 066,ITEMS!$B$3087,ITEMS!$B$3108,ITEMS!$B$3129,ITEM S!$B$3150,ITEMS!$B$317 1,ITEMS!$B$3192,ITEMS!$B$3213,ITEMS!$B$3234,ITEMS! $B$3255,ITEMS!$B$3276, ITEMS!$B$3297,ITEMS!$B$3318,ITEMS!$B$3339,ITEMS!$B $3360,ITEMS!$B$3381,IT EMS!$B$3402,ITEMS!$B$3423,ITEMS!$B$3444,ITEMS!$B$3 465,ITEMS!$B$3486,ITEM S!$B$3507,ITEMS!$B$3528,ITEMS!$B$3549,ITEMS!$B$357 0,ITEMS!$B$3591,ITEMS! $B$3612,ITEMS!$B$3633,ITEMS!$B$3654,ITEMS!$B$3675, ITEMS!$B$3696,ITEMS!$B $3717,ITEMS!$B$3738,ITEMS!$B$3759,ITEMS!$B$3780,IT EMS!$B$3801,ITEMS!$B$3 822,ITEMS!$B$3843,ITEMS!$B$3864,ITEMS!$B$3885,ITEM S!$B$3906,ITEMS!$B$392 7,ITEMS!$B$3948,ITEMS!$B$3969,ITEMS!$B$3990,ITEMS! $B$4011,ITEMS!$B$4032, ITEMS!$B$4053,ITEMS!$B$4074,ITEMS!$B$4095,ITEMS!$B $4116,ITEMS!$B$4137,IT EMS!$B$4156,ITEMS!$B$4179,ITEMS!$B$4220 There are too many cell references so I can not get this to work. Is there another way to define names with so many cell references? Any help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
I don't follow exactly which cells you want to define, your description
below is slightly inconsistent with the long address you posted. The following makes almost the same range as Tom's but not quite due to discrepancies in your descriptions, should be easy enough for you to adjust though. Test in a new workbook. Sub test() Dim i As Long Dim rng As Range Dim ws As Worksheet On Error Resume Next Set ws = ActiveWorkbook.Worksheets("Item") On Error GoTo 0 If ws Is Nothing Then ' you shouldn't won't need this bit Set ws = ActiveWorkbook.Worksheets.Add ws.Name = "Item" End If With ws Set rng = .Range("B2") ' row 2 For i = 21 To (21 * 200) Step 21 ' starts row 21, 19 below B2 ? Set rng = Union(rng, .Cells(i, 2)) Next End With ActiveWorkbook.Names.Add "jpgCells", rng 'or 'rng.Name = "jpgCells" Set rng = Nothing ''''''''''''''''''''''''''''''' ' only for testing Set rng = Range("jpgCells") ws.Columns(1).ClearFormats rng.Interior.ColorIndex = 6 rng.Select 'MsgBox rng.Count & " cells" i = 0 For Each rng In Range("jpgCells").Cells i = i + 1 Cells(i, 4) = rng.Address(0, 0) Next End Sub Bear in mind this is pretty close to the 224 limit I mentioned earlier for single cell areas in a defined name. In your double click event - If Not Intersect(Range("jpgCells"), Target(1)) Is Nothing Then ' open the jpg with filename in Target(1) Regards, Peter T "Chris Hankin" wrote in message ... Thanks Susan, Peter T and Charles Williams. In column B of my spreadsheet, I need to name a range of cells called: jpgCells. This occurs every 21 rows for 200 instances (except the very first instance). This will enable a user to double-click on these cells to insert a jpg picture. Can I name each of these cells in a different way to achieve my outcome? More help would be greatly appreciated. Thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
In article ,
Tom Ogilvy wrote: I stand corrected. His later post says every 21 is what he wants. I guess he had some typos: I didn't check *every* interval, but it appeared that only the first and last were less than 21. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
There was one or two others, enough that a mechanical check failed.
-- Regards, Tom Ogilvy "JE McGimpsey" wrote: In article , Tom Ogilvy wrote: I stand corrected. His later post says every 21 is what he wants. I guess he had some typos: I didn't check *every* interval, but it appeared that only the first and last were less than 21. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Name Define Help Please
Thanks Tom Ogilvy, J E McGimpsey and Peter T, all your help is
excellent and greatly appreciated. Cheers, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Name Define | Excel Discussion (Misc queries) | |||
insertnamedefine | Excel Discussion (Misc queries) | |||
InsertNameDefine | Excel Worksheet Functions | |||
Insert / Name / Define | Excel Discussion (Misc queries) | |||
Insert-Name-Define limit ? | Excel Discussion (Misc queries) |