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

Question

I am going slightly crazy on this and I'm not sure if it's because I'm searching for something that isn't possible or I'm just not searching in the right places! 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.

I've been trying to adopt so many different bits of code from youtube tutorials and forum posts that I cannot truly figure out where I went wrong. Right now the array sends only the data and not the index number to the array. I'm sure it's something simple but I've just stared at it for too long and can't figure it out. Any help is greatly appreciated.

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 

Answer 1

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 

Answer 2

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 

Answer 3

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