ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Insert Name Define Help Please (https://www.excelbanter.com/excel-programming/386351-insert-name-define-help-please.html)

Longbow
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 ***

Susan
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***

Peter T
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 ***

Charles Williams
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***

Chris Hankin[_3_]
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 ***

Tom Ogilvy
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 ***

JE McGimpsey
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 ***

Tom Ogilvy
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 ***

Tom Ogilvy
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 ***

Peter T
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 ***

JE McGimpsey
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.

Tom Ogilvy
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.

Chris Hankin[_3_]
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 ***


All times are GMT +1. The time now is 10:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com