Populating List Box with An Array - Reverse the Order?

eddiev1972

New Member
Joined
Mar 22, 2010
Messages
38
Thank you for in advance for any assistance. There is probably a very easy answer to this question and I just don't see it.

I am populating a list box with a 2 dimensional array: Data(1 to 3, 1 to x). The data I am using is 3 categories (period name, revenue, net operating income). The number of periods varies based on if the cell is populated with data.

I get everything to work, however, the data in the list box is displayed from the oldest to the newest and I want it to run in the reverse order (newest to oldest) I can't seem to get it to work. Below is the code that is working from oldest to newest

Code:
Sheets("Revenue").Activate
 
CountDown = Application.WorksheetFunction.CountA(Range("a:a"))
Set SearchingRange = Range(Cells(3, 1), Cells(CountDown, 1))
Set FindHotel = SearchingRange.Find(HotelName)
 
CountRight = Application.WorksheetFunction.CountA(Range("2:2"))
For Each Cell In Range(FindHotel, FindHotel.Offset(0, CountRight))
If Cell.Value <> "" Then
ActiveRight = ActiveRight + 1
End If
Next Cell
 
ReDim Data(1 To ActiveRight, 1 To 3)
 
For i = 1 To ActiveRight
Data(i, 1) = Range("a2").Offset(0, i)
Next i
 
For i = 1 To ActiveRight
Data(i, 2) = FormatCurrency(FindHotel.Offset(0, i))
Next i
 
Sheets("Gop").Activate
 
CountDown = Application.WorksheetFunction.CountA(Range("a:a"))
Set SearchingRange = Range(Cells(3, 1), Cells(CountDown, 1))
Set FindHotel = SearchingRange.Find(HotelName)
 
For i = 1 To ActiveRight
Data(i, 3) = FormatCurrency(FindHotel.Offset(0, i))
Next i
 
ListBox1.ColumnCount = 3
ListBox1.List = Data
When popoulating the Data array I have tried

Code:
for i = ActiveRight to 1 step -1
Data(i, 3) = FormatCurrency(FindHotel.Offset(0, i))
next i
But that gives the same results. Thank for any suggestions.
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,363
Try:
Code:
For i = 1 To ActiveRight
    Data(ActiveRight + 1 - i, 3) = FormatCurrency(FindHotel.Offset(0, i))
Next
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,781
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top