For Loop to Create Data, Save to Array, and Display array on Worksheet

Question

I am trying to use a for loop to create a series of data, assign that data and the index number to an array, and then send that array to a worksheet.

This sends only the data and not the index number to the array.

Option Explicit

Sub MonthlyObligation()
    Dim ws As Worksheet
    Set ws = Sheets("Page 1")
    
    ' 1. Define the array
    Dim arr() As Variant
    ' 2. Generate Data with for Loop
        ' 2a. Determine the number of rows in the array
    Dim BaseTerm As Integer
    Dim OptionCount As Integer
    Dim OptionTerm As Integer
    
    BaseTerm = ws.Range("LeaseTerm").Value
    OptionCount = ws.Range("RenewalOptions").Value
    OptionTerm = ws.Range("TermPerOption").Value
        
    Dim TotalTerm As Long
    TotalTerm = ((BaseTerm + (OptionCount * OptionTerm)) / 12)
        ' 2b. Redefine the array to the row count
    ReDim arr(1 To TotalTerm, 1)
        ' 2c. Create Data for the array
    Dim MonthlyBaseRent As Double, AnnualRiser As Double, currentRow As Long
        
    MonthlyBaseRent = ws.Range("BaseRent").Value
    AnnualRiser = ws.Range("RentIncrease").Value
    
    Dim i As Long, j As Long
        For i = 1 To TotalTerm
        
            For j = 1 To 1
            MonthlyBaseRent = (MonthlyBaseRent * (1 + AnnualRiser))
            ' 2a. Store the Data in the Array
            arr(i, j) = MonthlyBaseRent
            Debug.Print arr([i], [j])
            Next j
        Next i
End Sub

What is the "index number"? Do you mean i? ReDim arr(1 To TotalTerm, 1) is the same as ReDim arr(1 To TotalTerm, 0 To 1) - is that what you want? If you want to store two values pr row then ReDim arr(1 To TotalTerm, 1 To 2) might be better. — Tim Williams
– Tim Williams, Commented Apr 24 at 6:02
You have an inner loop For j = 1 To 1 . If this is deliberate, it is totally unnecessary, as it executes only once. This would also mean that a single dimensional array would suffice. — Jonathan Willcock
– Jonathan Willcock, Commented Apr 24 at 7:40
Thanks! That makes sense. I was making it more complicated than it needed to be. — Squid1622
– Squid1622, Commented Apr 25 at 4:03

CHill60 · Accepted Answer · 2025-04-24 09:06:25Z

2

The "index" is not going into the array, because you are not assigning it into the array!

You want the for-loop to look something like this

    Dim i As Long, j As Long
    For i = 1 To TotalTerm
    
        MonthlyBaseRent = (MonthlyBaseRent * (1 + AnnualRiser))
        ' 2a. Store the Data in the Array
        arr(i, 0) = i
        arr(i, 1) = MonthlyBaseRent
        Debug.Print arr(i, 0), arr(i, 1)
    
    Next i

Note the change to the line with Debug.Print I am dumping both values. I've lost the unnecessary inner loop on j because it wasn't doing anything at all. I've also removed the square brackets from

Debug.Print arr([i], [j])

Square brackets are the same as Evaluate("i") - which happens to work in this case because the variables are in scope, but is harder to maintain, and not required at all.

As @Jonathan Willcock has pointed out, you don't really need to add the index into the array at all. You could just use a single-dimensional array like this

ReDim arr(1 To TotalTerm)

    Dim i As Long, j As Long
    For i = 1 To TotalTerm
    
        MonthlyBaseRent = (MonthlyBaseRent * (1 + AnnualRiser))
        ' 2a. Store the Data in the Array
        arr(i) = MonthlyBaseRent
        Debug.Print i, arr(i)
    
    Next i

answered Apr 24 at 9:06

CHill60

2,0711 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Squid1622 Apr 25 at 3:59

Thanks for helping me understand why the index wasn't assigning to the array. I'll try the solution tomorrow. I want to store the index in the array as a dimension so I can use it as a period value. The indexes correspond to financial periods.

CDP1802 · Accepted Answer · 2025-04-25 19:06:44Z

Option Explicit

Sub MonthlyObligation()
    
    Dim ws As Worksheet, arr
    Dim BaseTerm As Long, OptionCount As Long, OptionTerm As Long
    Dim MonthlyBaseRent As Double, AnnualRiser As Double
    Dim TotalTerm As Long, i As Long
    
    Set ws = Sheets("Page 1")
    With ws
        BaseTerm = 12  '.Range("LeaseTerm").Value
        OptionCount = 5 '.Range("RenewalOptions").Value
        OptionTerm = 4 '.Range("TermPerOption").Value
        MonthlyBaseRent = 1000 '.Range("BaseRent").Value
        AnnualRiser = 0.1  '.Range("RentIncrease").Value
    End With
    
    ' total period in month
    TotalTerm = BaseTerm + (OptionCount * OptionTerm)
    ' resize array
    ReDim arr(1 To TotalTerm, 1 To 2)
    
    For i = 1 To TotalTerm
           
        ' fill array
        arr(i, 1) = i
        arr(i, 2) = MonthlyBaseRent
        
        ' annual increase after 12 months
        If i Mod 12 = 0 Then
            MonthlyBaseRent = MonthlyBaseRent * (1 + AnnualRiser)
        End If
    Next i
    
    ' write to sheet
    ws.Range("A1:B1") = Array("Period", "Rent")
    ws.Range("A2:B2").Resize(UBound(arr)) = arr
End Sub

Adrian · Accepted Answer · 2025-04-27 14:58:53Z

0

I think I must know more economic and financial calculation to answer this. I shall start from your existing VBA code and see, what could be improved. At the first sight, could be a matter of arrays indexing, if I'm not wrong too much. The AnnualRiser can be associated with the i index, and the j index could be get as a month counter. Not sure about MonthlyBaseRent formula

Option Explicit
Option Base 0

Sub MonthlyObligation()
    Dim ws As Worksheet
    Set ws = Sheets("Page 1")
    '' 1. Define the array
    
    Dim arr() As Variant
    
    '' 2. Generate Data with for Loop  
    '' 2a. Determine the number of rows in the array
    
    Dim BaseTerm As Integer
    Dim OptionCount As Integer
    Dim OptionTerm As Integer
    
    BaseTerm = ws.Range("LeaseTerm").Value
    OptionCount = ws.Range("RenewalOptions").Value
    OptionTerm = ws.Range("TermPerOption").Value    
    
    Dim TotalTerm As Long
    TotalTerm = ((BaseTerm + (OptionCount * OptionTerm)) / 12) 
    
    '' It seems like BaseTerm is expressed in month and TotalTerm in years
    
    '' A particular case would be, when TotalTerm gets 0
    '' This is the reason, for the index will not allow iterating the array
    '' Use Option Base 0 at beginning your script, 
    '' if you do not want to change TotalTerm. But the code will have to change 
    
    ''If (TotalTerm = 0) Then 
    ''    TotalTerm = 1 
    ''End If 
    
    '' 2b. Redefine the array to the row count
    ReDim arr(0 To TotalTerm, 0 To 11)
    '' 2c. Create Data for the array
    
    Dim MonthlyBaseRent As Double, AnnualRiser As Double, currentRow As Long  
    
    MonthlyBaseRent = ws.Range("BaseRent").Value
    AnnualRiser = ws.Range("RentIncrease").Value
    
    Dim i As Long, j As Long
    Dim totalRent As Double: totalRent = 0#
    
    For i = 0 To TotalTerm Step 1 
        MonthlyBaseRent = (MonthlyBaseRent * (1 + AnnualRiser)) 
        For j = 0 To 11 Step 1
            '' 2a. Store the Data in the Array
            totalRent = totalRent + MonthlyBaseRent
            arr(i, j) = totalRent
            ''If (j + 1 = Month(Now)) and (i = TotalTerm) Then Exit For
        Next j 
    Next i
    
    ''' Then you'll have to see what to do with data stored into the array 
    
    Dim start As Integer : start = 10 '' Set row number, where to begin writing
    For i = 0 To TotalTerm Step 1 
        currentRow = i + start
        For j = 0 To 11 Step 1 
            ws.range(Chr(65 + j + 1) & currentRow).value = arr(i, j)
        Next j 
    Next i 
    
End Sub

edited Apr 27 at 14:58

answered Apr 24 at 13:04

Adrian

524 bronze badges

4 Comments

Tim Williams Apr 24 at 18:09

Did you test this? The second dimension of your arr only has two slots, not 12.

Adrian Apr 25 at 6:13

I shall change the second dimension of the array from 1 to 12. There are 12 months a year. But I think the iteration should stop (use a break statement), when it reaches the current year / month date

Adrian Apr 27 at 9:00

I had changed dimensions of the array. Also added code for writing data to the worksheet. It is a new variable totalRent, which has the current value per each month.

Adrian Apr 27 at 15:15

It seems like TotalTerm sometimes gets 0 for a shorter time, and dimensioning the array from 1 To 0 cannot be possible. Use Option Base 0 directive at beginning your script, and index the arrays starting from 0, not from 1.

Collectives™ on Stack Overflow

For Loop to Create Data, Save to Array, and Display array on Worksheet

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related