Hard to believe.

Your formula doesn't make much sense.

If you base your formula on A2, then count the entries in A996:A65534 to get
the row count, don't see how this could define the correct range.

It appears you formula is using relative references and will vary based on
the active Cell. If you clean up your formula, then it should work.

--
Regards,
Tom Ogilvy


"Steve" <No Spam> wrote in message news:436d5b5c$1@dnews.tpgi.com.au...
> The dynamic range sets marching ants around the correct range.
>
> "Steve" <No Spam> wrote in message news:436d5afd@dnews.tpgi.com.au...
> > Tom
> >
> > It's not picking up my dynamic named range:
> >
> > I have 'TeamArray' defined as:
> >
=OFFSET(TeamDetail!$A$2,0,0,COUNTA(TeamDetail!$A65534:$A996),COUNTA(TeamDeta
il!$2:$2))
> >
> > Code below was modified appropriately by copying the name "TeamArray "
> > from the Define Name and pasting over DynamicDataRange (ie no spelling
> > mistake).
> >
> > Steve
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:ersb%235m4FHA.1476@TK2MSFTNGP10.phx.gbl...
> >> Try it this way:
> >>
> >> Private Sub UserForm_Initialize()
> >> Dim rArray As Variant
> >> Dim rng as Range
> >> On error Resume Next
> >> set rng = Thisworkbook.Names("DynamicDataRange").RefersToRange
> >> On error goto 0
> >> if rng is nothing then
> >> msgbox "No range named DynamicDataRange"
> >> exit sub
> >> end if
> >> rArray = rng.Value
> >> With Me.ComboBox3
> >> ' use the next line
> >> .List() = rArray
> >> ' or the next two lines
> >> '.RowSource = "Data!A4:C23"
> >> '.ColumnHeads = True
> >> .ListIndex = -1 ' no selected item
> >> End With
> >> End Sub
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Steve" <No Spam> wrote in message news:436d50c2$1@dnews.tpgi.com.au...
> >>> Hi
> >>>
> >>> Excel 2K
> >>>
> >>> I need to fill a combo box from a dynamic range compiled by users. The
> >> first
> >>> column (bound column, not visible) will contain letters for use in a
> >> "begins
> >>> with" filter. The second column (visible, not bound) will contain the
> >>> description to be seen by users on the form.
> >>>
> >>> I found an example how to fill a multi-column combo in a form at
> >>> http://www.erlandsendata.no/english/
> >>> The example works. However, when I transfer the form to my spreadsheet
> >>> and
> >>> rename the dynamic range to my dynamic range, the code generates an
> >>> error
> >> at
> >>> rArray = Range("DynamicDataRange"). Run-time errot 1004 Method
'Range'
> >>> of
> >>> object Global failed. Pressing help at this point produce an
informative
> >>> blank page!
> >>>
> >>> My initial thougt was a need to size the array so, I tested with a
fixed
> >>> size rArray. Still received an error. Can comone give me some help in
> >>> filling a two column combo from a dynamic named range?
> >>>
> >>> Steve
> >>>
> >>> The Erlandsen code:
> >>>
> >>> Private Sub UserForm_Initialize()
> >>> Dim rArray As Variant
> >>>
> >>> rArray = Range("DynamicDataRange")
> >>> With Me.ComboBox3
> >>> ' use the next line
> >>> .List() = rArray
> >>> ' or the next two lines
> >>> '.RowSource = "Data!A4:C23"
> >>> '.ColumnHeads = True
> >>> .ListIndex = -1 ' no selected item
> >>> End With
> >>> End Sub
> >>>
> >>>
> >>
> >>
> >
> >
>
>