Create Data List with Variable Number of Cells

[TheDenouement]

Is there a way to create some kind of output into multiple cells?

For instance, I have a dollar amount that I want to increase by 5% each year but I don't know how many years yet. But based on user input (say 10yrs) 10 cells would be created, each 5% larger than the next?

 

[WinteE]

Copy this code into the Object of the sheet you need this in.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Variant

For x = 0 To 9
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        If x = 0 Then
            Range("B5").Value = Target.Value * 1.05
        Else
            Range("B5").Offset(x, 0).Value = Range("B5").Offset(x - 1, 0).Value * 1.05
        End If
    End If
Next x

End Sub

When entering a value in cell B3 a list will be created in the cells B5:B14

Success

Erik

 

[TheDenouement]

Great! I am very slowly learning to understand enough VBA to manipulate it. VERY slowly.

What type of code would I use to have it clear the data and replace it whenever new input is typed?

Do I need special code to have text automatically generated into adjacent cells to annotate the results?

Thanks for all the help!

 

[WinteE]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Variant
Dim i As Variant

i = Cells(Cells.Rows.Count,"B").End(xlUp).Row
Range("B5:B" & i).ClearContents

For x = 0 To 9
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        If x = 0 Then
            Range("B5").Value = Target.Value * 1.05
            Range("B5").Offset(0,1).Value = "Text" & 0
        Else
            Range("B5").Offset(x, 0).Value = Range("B5").Offset(x - 1, 0).Value * 1.05
            Range("B5").Offset(x, 1).Value = "Text" & x
        End If
    End If
Next x

End Sub

Be aware that all data from cell B5 till the last value in column B will be cleared.