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:
<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>
<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> CommandButton1_Click()
<font color='#000080'>Dim</font> arr() <font color='#000080'>As</font> <font color='#000080'>Variant</font>
<font color='#000080'>Dim</font> g <font color='#000080'>As</font> <font color='#000080'>Integer</font>
<font color='#000080'>Dim</font> intCount <font color='#000080'>As</font> <font color='#000080'>Integer</font>
<font color='#000080'>Dim</font> rngValid <font color='#000080'>As</font> Range
<font color='#000080'>Dim</font> intNumItems <font color='#000080'>As</font> <font color='#000080'>Integer</font>
<font color='#000080'>Set</font> rngValid = Range("B3", Range("B3").End(xlDown))
intNumItems = Application.WorksheetFunction.CountIf(rngValid, ">" & Now())
<font color='#000080'>ReDim</font> arr(1 To intNumItems, 1 To 2)
<font color='#000080'>For</font> g = 3 To Range("A3").End(xlDown).Row
<font color='#000080'>If</font> Cells(g, 2).Value > Now() <font color='#000080'>Then</font>
intCount = intCount + 1
arr(intCount, 1) = Cells(g, 1)
arr(intCount, 2) = Cells(g, 2)
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>Next</font> g
ListBox4.List() = arr
<font color='#000080'>End</font> <font color='#000080'>Sub</font>
</font></pre>