Defined named range (Array list)

[Sinner]

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.

 

[Peter T]

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]

Thx Peter.
Appreciate that.

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










- Show quoted text -