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" <ims...@gmail.com> wrote in message
>
> news:ac708ea4-4f92-4edc-b040-(E-Mail Removed)...
>
>
>
> > 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 -
Similar Threads
