+ Reply to Thread
Results 1 to 3 of 3

Zero Length Dynamic Range

Hybrid View

  1. #1
    Ken McLennan
    Guest

    Zero Length Dynamic Range

    G'day there One & All,

    Stumped again, so here I am once more =). I have a number of
    dynamic ranges to handle various lists in my project. I used 2 ranges on
    one list to overcome a problem I couldn't find another way around. My
    list has entries such as:

    Sections <-- Heading
    Unknown
    Alpha
    Bravo
    Charlie
    etc...

    The entries are for testing only, as they will be set by the clients
    once this is all sorted out. The only constant entries are the Heading,
    and the "Unknown" entry. This doesn't designate an unknown quantity, but
    is the string "Unknown". There will be no other entries when deployed.
    The users will enter them via a form as required.

    I have a dynamic range "Sections" of all entries including "Unknown". A
    second one, "srtSections" is for everything under "Unknown" - Alpha -
    Charlie in this case. That way my forms ListBox has a RowSource of
    "Sections" which includes everything, but when the user adds or deletes
    an entry I use "srtSections" and sort alphabetically. This leaves
    "Unknown" on top of the ListBox at all times. I use ListBox.Listcount to
    ensure that the last entry can't be removed.

    All was working fine, until I tested to see how it all went & realised
    that although I couldn't delete "Unknown" (correct behaviour), once I
    got down to only that entry then "srtSections" was a range of zero
    entries and my attempts to reference it raised Global Range errors.

    The code is here:

    Private Sub CommandButton6_Click()
    ' Add Section
    ' Set listbox rowsource to null preventing interference with dynamic
    ' range calculations
    Me.ListBox1.RowSource = ""
    ' Set range variable to address of dynamic range after "Unknown"
    ' entry
    If Application.WorksheetFunction.IsError(Range
    ("srtSections").Cells.Count) Then
    dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
    Else
    Set gTgt = Range("srtSections")
    ' Add value onto bottom of list
    Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
    End If
    ' Sort list in alphabetical order
    Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
    Order1:=xlAscending
    ' Set listbox rowsource to full list of data as set by dynamic range
    ' including "Unknown" entry
    Me.ListBox1.RowSource = "Data!Sections"

    End Sub

    I'm trying to add a member to my list, but the "srtSections" reference
    has me stuck. How can I test for a range of zero cells without
    generating an error? The "IsError" function I used doesn't work as the
    error stops everything before I can test for it. Any ideas anyone?

    See ya, thanks for thinking about it
    Ken McLennan
    Qld, Australia

  2. #2
    Tom Ogilvy
    Guest

    Re: Zero Length Dynamic Range

    If gTgt is declared as a global variable and is of type range, then delete
    the Dim statement below.
    If it is a global variable, but not of type range, then you will need to use
    another local variable which is dim'd as range to do the test.

    Private Sub CommandButton6_Click()
    Dim gTgt as Range
    ' Add Section
    ' Set listbox rowsource to null preventing interference with dynamic
    ' range calculations
    Me.ListBox1.RowSource = ""
    ' Set range variable to address of dynamic range after "Unknown"
    ' entry
    On Error Resume Next
    set gTgt = Range("srtSections")
    On Error goto 0
    If rng is nothing Then
    dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
    Else
    ' Set gTgt = Range("srtSections")
    ' Add value onto bottom of list
    Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
    End If
    ' Sort list in alphabetical order
    Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
    Order1:=xlAscending
    ' Set listbox rowsource to full list of data as set by dynamic range
    ' including "Unknown" entry
    Me.ListBox1.RowSource = "Data!Sections"

    End Sub


    --
    Regards,
    Tom Ogilvy

    "Ken McLennan" <[email protected]> wrote in message
    news:[email protected]...
    > G'day there One & All,
    >
    > Stumped again, so here I am once more =). I have a number of
    > dynamic ranges to handle various lists in my project. I used 2 ranges on
    > one list to overcome a problem I couldn't find another way around. My
    > list has entries such as:
    >
    > Sections <-- Heading
    > Unknown
    > Alpha
    > Bravo
    > Charlie
    > etc...
    >
    > The entries are for testing only, as they will be set by the clients
    > once this is all sorted out. The only constant entries are the Heading,
    > and the "Unknown" entry. This doesn't designate an unknown quantity, but
    > is the string "Unknown". There will be no other entries when deployed.
    > The users will enter them via a form as required.
    >
    > I have a dynamic range "Sections" of all entries including "Unknown". A
    > second one, "srtSections" is for everything under "Unknown" - Alpha -
    > Charlie in this case. That way my forms ListBox has a RowSource of
    > "Sections" which includes everything, but when the user adds or deletes
    > an entry I use "srtSections" and sort alphabetically. This leaves
    > "Unknown" on top of the ListBox at all times. I use ListBox.Listcount to
    > ensure that the last entry can't be removed.
    >
    > All was working fine, until I tested to see how it all went & realised
    > that although I couldn't delete "Unknown" (correct behaviour), once I
    > got down to only that entry then "srtSections" was a range of zero
    > entries and my attempts to reference it raised Global Range errors.
    >
    > The code is here:
    >
    > Private Sub CommandButton6_Click()
    > ' Add Section
    > ' Set listbox rowsource to null preventing interference with dynamic
    > ' range calculations
    > Me.ListBox1.RowSource = ""
    > ' Set range variable to address of dynamic range after "Unknown"
    > ' entry
    > If Application.WorksheetFunction.IsError(Range
    > ("srtSections").Cells.Count) Then
    > dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text
    > Else
    > Set gTgt = Range("srtSections")
    > ' Add value onto bottom of list
    > Cells(gTgt.Rows.Count + 3, gTgt.Column).Value = TextBox3.Text
    > End If
    > ' Sort list in alphabetical order
    > Range("srtSections").Sort Key1:=Range("Sections").Cells(2, 1),
    > Order1:=xlAscending
    > ' Set listbox rowsource to full list of data as set by dynamic range
    > ' including "Unknown" entry
    > Me.ListBox1.RowSource = "Data!Sections"
    >
    > End Sub
    >
    > I'm trying to add a member to my list, but the "srtSections" reference
    > has me stuck. How can I test for a range of zero cells without
    > generating an error? The "IsError" function I used doesn't work as the
    > error stops everything before I can test for it. Any ideas anyone?
    >
    > See ya, thanks for thinking about it
    > Ken McLennan
    > Qld, Australia




  3. #3
    Ken McLennan
    Guest

    Re: Zero Length Dynamic Range

    G'day there Tom,

    > If gTgt is declared as a global variable and is of type range, then delete
    > the Dim statement below.


    Yes, it's a global variable of type range. I got that bit right =)

    > Private Sub CommandButton6_Click()

    ....
    > On Error Resume Next
    > set gTgt = Range("srtSections")
    > On Error goto 0
    > If rng is nothing Then
    > dSht.Range(3, Range("Sections").Column).Value = TextBox3.Text

    ....
    > End Sub


    ..."is nothing"!!! It's so simple once you point out where I
    went wrong <g>.

    Thanks very much for that. I've not put it to work as yet, as my
    wife won't let me play on my 'pooter until we've done the grocery
    shopping ...dammit!! (She has some strange idea that groceries don't
    just magically appear in the cupboards when you need them).

    See ya, and thanks once again,
    Ken McLennan
    Qld, Australia

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1