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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

John_w

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

Watch MrExcel Video

Forum statistics

Threads
1,132,875
Messages
5,655,754
Members
418,234
Latest member
jdorfma

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