Defined named range (Array list)
 

Hi,

I have a defined name 'ActivityList' with refer to:
={"Basic","Prime"...................."} and so on with move than 13 to
15 entries upto 30+ which keep on updating.

Basically using it with sumproduct formula.
=SUMPRODUCT(--(ActivityList=$B$25),(AMTList)+(DiscountList),--
(DateList=O$5))

The problem I'm having is that it does not allow more than 13 to 15
entries.

Any work around would be appreciated.

Defined named range (Array list)
 

You are being hit by the 255 limit of the refersto string. Try this on a new
sheet -

Sub test()
Dim i As Long, n As Long
Dim rName As Range, rng As Range

For i = 1 To 200 Step 2
With Cells(i, 1)
n = n + 1
.Name = "theCell_" & Right("0" & n, 3)
.Value = 1
End With
Next

For n = 1 To 100
Set rName = Range("theCell_" & Right("0" & n, 3))
If rng Is Nothing Then
Set rng = rName
Else
Set rng = Union(rng, rName)
End If
Next

ActiveWorkbook.Names.Add "BigName", rng

Range("BigName").Select
MsgBox Selection.Count
End Sub

With this method you'll be limited to between 149-224 areas in the big name,
depending on the combination of single & multiple cells. With other methods
you can include several thousand non-contiguous ranges in a single name, but
requires quite a bit of code to manage it all.

Regards,
Peter T


"Sinner" wrote in message
...
Hi,

I have a defined name 'ActivityList' with refer to:
={"Basic","Prime"...................."} and so on with move than 13 to
15 entries upto 30+ which keep on updating.

Basically using it with sumproduct formula.
=SUMPRODUCT(--(ActivityList=$B$25),(AMTList)+(DiscountList),--
(DateList=O$5))

The problem I'm having is that it does not allow more than 13 to 15
entries.

Any work around would be appreciated.

Defined named range (Array list)
 

Thx Peter.
Appreciate that.


On Feb 16, 5:00*pm, "Peter T" <peter_t@discussions wrote:
You are being hit by the 255 limit of the refersto string. Try this on a new
sheet -

Sub test()
Dim i As Long, n As Long
Dim rName As Range, rng As Range

* * For i = 1 To 200 Step 2
* * * * With Cells(i, 1)
* * * * * * n = n + 1
* * * * * * .Name = "theCell_" & Right("0" & n, 3)
* * * * * * .Value = 1
* * * * End With
* * Next

* * For n = 1 To 100
* * * * Set rName = Range("theCell_" & Right("0" & n, 3))
* * * * If rng Is Nothing Then
* * * * * * Set rng = rName
* * * * Else
* * * * * * Set rng = Union(rng, rName)
* * * * End If
* * Next

* * ActiveWorkbook.Names.Add "BigName", rng

* * Range("BigName").Select
* * MsgBox Selection.Count
End Sub

With this method you'll be limited to between 149-224 areas in the big name,
depending on the combination of single & multiple cells. *With other methods
you can include several thousand non-contiguous ranges in a single name, but
requires quite a bit of code to manage it all.

Regards,
Peter T

"Sinner" wrote in message

...



Hi,

I have a defined name 'ActivityList' with refer to:
={"Basic","Prime"...................."} and so on with move than 13 to
15 entries upto 30+ which keep on updating.

Basically using it with sumproduct formula.
=SUMPRODUCT(--(ActivityList=$B$25),(AMTList)+(DiscountList),--
(DateList=O$5))

The problem I'm having is that it does not allow more than 13 to 15
entries.

Any work around would be appreciated.- Hide quoted text -

- Show quoted text -