Dual Column List Box
Dual Column List Box
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Dual Column List Box

  1. #1
    Guest

    Default

     
    I have a list of documents in column A and their
    corresponding expiration dates in column B. Using the
    following code, I can filter out all documents that have
    expired and display the document names in the list box.
    How can I get their correspondind expiration dates to be
    listed next to them?

    For g = 1 To Range("A3").End(xlDown).Row
    If Cells(g, 2).Value < Now() Then ListBox4.AddItem Cells
    (g, 1)
    Next g

    Thanks,
    Greg

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-22 12:28, Anonymous wrote:
    I have a list of documents in column A and their
    corresponding expiration dates in column B. Using the
    following code, I can filter out all documents that have
    expired and display the document names in the list box.
    How can I get their correspondind expiration dates to be
    listed next to them?

    For g = 1 To Range("A3").End(xlDown).Row
    If Cells(g, 2).Value < Now() Then ListBox4.AddItem Cells
    (g, 1)
    Next g

    Thanks,
    Greg
    Ok, I changed the Value of g to start at 3 since it looks like your data starts in A3, but you can modify to suit your needs. Make sure you change your ColumnCount property to 2 in your List Box and try something like this - hope it helps:



    Option Explicit


    Private Sub CommandButton1_Click()
    Dim arr() As Variant
    Dim g As Integer
    Dim intCount As Integer
    Dim rngValid As Range
    Dim intNumItems As Integer

    Set rngValid = Range("B3", Range("B3").End(xlDown))

    intNumItems = Application.WorksheetFunction.CountIf(rngValid, ">" & Now())

    ReDim arr(1 To intNumItems, 1 To 2)

    For g = 3 To Range("A3").End(xlDown).Row
    If Cells(g, 2).Value > Now() Then
    intCount = intCount + 1
    arr(intCount, 1) = Cells(g, 1)
    arr(intCount, 2) = Cells(g, 2)
    End If
    Next g

    ListBox4.List() = arr
    End Sub


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com