# Populating table with VBA



## pholt33 (Dec 21, 2022)

I am working on a file to show the impact of investing in multiple properties over time. I have the base all set and would like a way to update additional fields based on input box, for example (in words) "buy 1 additional property every 2 years for 10 years."  
In the screenshot, it would populate whole numbers in J4:J22 like in the table on the right.  D4 is a whole number.

I can do it with formulas (2nd screenshot) but I want the flexibility to also manually input in J4:J22.

How can I do this with VBA?  

Thanks!


----------



## pholt33 (Dec 21, 2022)

Got it figured out by modifying some other code I found.




```
Sub InputBoxYears()

    Dim YearInterval As Double
    Dim YearNumber As Double
   
    Application.ScreenUpdating = False
   
    YearInterval = Application.InputBox(prompt:="Every X years", Title:="Input year interval", Type:=1)
    YearNumber = Application.InputBox(prompt:="For Y years", Title:="Input number of years", Type:=1)
   
    Range("SecondPurchase") = Range("FirstPurchase") + YearInterval
 
     For Each Cell In Range("Years3to20")
        If Cell.Value <= (YearNumber / YearInterval) Then
            Cell.Offset(0, 1).Value = Cell.Offset(-1, 1).Value + YearInterval
        End If
    Next Cell

 End Sub
```


----------

