Using an array to calculate in what year to build sport arenas

[Waimea]

I have a data table with cities, build year, number of seats, need for seats and the current number of seats.

I am trying to calculate in what year a company should build sports arenas and in what city. I have read about static arrays and I know the size of my array.

However, I am not sure where to begin with how to manipulate the array.

Step1. Load range into an array
Step2. Sort array in the correct order (ex. 2011, 2016, 2020, 2025, etc).
Step3. Calculate the need for seats minus the number of seats being built in the corresponding year
Step4. Return result to a spreadsheet

Desired output:
We should build 2000 seats in City5 in the year 2027.

Data table:

[I]Column      2                        3                        4                 5                            6 [/I]
Number     City                    Need for seats     Build year     Seats being built     Current number of seats        
1               City1                  1500                  2020            2500                      2500   
2               City2                  7500                  2022            5500                      4500   
3               City3                  8500                  2023            4500                      5500   
4               City4                  3500                  2024            3500                      6500   
5               City5                  2500                  2026            7500                      8500

 

[Waimea]

Sub BuildYears()


' Get Range
    Dim rg As Range
    Set rg = ThisWorkbook.worksheets("Data").Range("M14:M44")


    ' Create dynamic array
    Dim BuildYear As Variant


    ' Read values into array from sheet1
    BuildYear = rg.Value
    
    ' Print the array values
    Debug.Print "a", "b", "Value"
    Dim a As Long, b As Long
    For a = LBound(BuildYear) To UBound(BuildYear)
            For b = LBound(BuildYear, 2) To UBound(BuildYear 2)
            Debug.Print a, b, BuildYear(a, b)
        Next b
    Next a
  End Sub

This is the code I have right now but I don't understand the use of LBound To UBound.

 

[Waimea]

Any suggestions on how I can manipulate the array?

I want to count the occurrence of each year, then sort the array it in the correct order.

 

[NdNoviceHlp]

Hope this gets U started. Dave

Sub BuildYears()
Dim rg As Range, tempr As Range, cnt As Integer
Dim BuildYear() As Variant
Set rg = ThisWorkbook.Worksheets("Data").Range("M14:M44")
' Read values into array from sheet1
cnt = 0
For Each tempr In rg
ReDim Preserve BuildYear(cnt)
BuildYear(cnt) = tempr '.Value
cnt = cnt + 1
Next tempr
' Print the array values
'Debug.Print "a", "b", "Value"
Dim a As Integer 'Long, b As Long
For a = LBound(BuildYear) To UBound(BuildYear)
MsgBox BuildYear(a)
' For b = LBound(BuildYear, 2) To UBound(BuildYear 2)
'Debug.Print a, b, BuildYear(a, b)
'Next b
Next a
End Sub

 

[Waimea]

Hi NdNoviceHlp,

thank you for your reply! I am looking at the code and I am trying to understand it.

 

[NdNoviceHlp]

Hope this gets U started. Dave

Sub BuildYears()
Dim rg As Range, tempr As Range, cnt As Integer
Dim BuildYear() As Variant
Set rg = ThisWorkbook.Worksheets("Data").Range("M14:M44")
' Read values into array from sheet1
cnt = 0
For Each tempr In rg
ReDim Preserve BuildYear(cnt)
BuildYear(cnt) = tempr '.Value
cnt = cnt + 1
Next tempr
' Print the array values
'Debug.Print "a", "b", "Value"
Dim a As Integer 'Long, b As Long
For a = LBound(BuildYear) To UBound(BuildYear)
MsgBox BuildYear(a)
' For b = LBound(BuildYear, 2) To UBound(BuildYear 2)
'Debug.Print a, b, BuildYear(a, b)
'Next b
Next a
End Sub

 

[NdNoviceHlp]

Not sure why I posted twice? Anyways, all the code does is load range M14:M44 into an array and then outputs the array via msgbox just to show U how arrays work. To do what U want U will likely need a multidimension array. So for instance, U have 5 cities and 4 areas of interest (need for seats, build yr, seats being built, and current number of seats) U would dimension the array ..
Dim BuildYear(5,4) as Variant
in array position BuildYear(0,0) data for yr 1 need for seats
in array position Buildyear(0,1) data for yr 1 seats being built etc
in array position BuildYear(1,0) data for yr 2 need for seats
in array position Buildyear(1,1) data for yr 2 seats being built etc
etc.
The array would then contain all of your information. Note that array positions begin at 0 (not 1) unless Option Base 1 is placed at the top of your code. HTH. Dave

 

[Waimea]

Hi Dave,

thank you for your reply and for your comments!

I am reading about VBA and maybe I want a dictionary or a collection?

 

[NdNoviceHlp]

Both would be good options... it really depends on how much data U have, is it static or changing, and what U want to do with it (along with some other stuff that I'm sure I've missed). Your displayed data really looks like U could achieve what U want with some nifty spreadsheet formulas which avoids using macros which may be important dependent upon what U want to do with your workbook. Anyways, good luck. Post if you need further assistance. Dave

 

[Waimea]

Hi, thank you for your reply.

My data is almost static since building arenas takes a long time. Do you have any good suggestions on how to calculate when the need for arenas is high?

I am trying to determine in what city and in what year I should build given how the competition is building arenas.