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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

John_w

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

Forum statistics

Threads
1,176,151
Messages
5,901,639
Members
434,909
Latest member
subratgupta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top