User input controlled data

[mabbutt]

Hi,

I am not sure if this is possible but I think if it is I might have to use VBA! Due to my lack of knowledge this will be a learning curve

This is what I am hoping is possible. When you enter a number into cell A1 it automatically fills out data below depending on the value.

For example, if the number entered is 5 then cell B1 will have "2014", cell B3 will have "2015" and so on until cell B9 has "2018". I want a gap between cells which is why it skips a cell. if the user puts in 20 then it will follow the same pattern but end in cell B39 with a value on 2033.

I'd then like to put a formula in cell C1, =SUM(C1*0.06+C1), and copy that down as far as the data in the B column went. Again missing a cell and matching the pattern in the B column.

All of this I am hoping is possible from a simple input of a number into cell A1.

If this isn't possible can anyone suggest an alternative way to achieve this?

Many thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

You can't have the formula:

=SUM(C1*0.06+C1),

in C1. That will create a circular reference.

 

[mabbutt]

Welcome to MrExcel.

You can't have the formula:

=SUM(C1*0.06+C1),

in C1. That will create a circular reference.

Hi Andrew,

Thank you you pointing that out. A typo on my part, the formula should go in C3.

Do you have any idea on if the rest is possible?

 

[Andrew Poulsom]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Columns("B:C").ClearContents
        .Range("B1").Value = 2014
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i).Value = .Range("B" & i - 2).Value + 1
            .Range("C" & i).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub

 

[mabbutt]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Columns("B:C").ClearContents
        .Range("B1").Value = 2014
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i).Value = .Range("B" & i - 2).Value + 1
            .Range("C" & i).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub

Hi Andrew,

This works perfectly! Thank you so much.

I have tweaked it slightly to fit my spreadsheet and I am struggling to understand how you insert the formula as this line of code is very complicated.

For example in column B for the year 2015 I would need the sum to be =SUM(B20*N5+B20)

This is my code from what you gave me:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address <> "$A$1" Then Exit Sub
    If Not IsNumeric(Target.Value) Then Exit Sub
    Application.EnableEvents = False
    With Me
        .Range("B20:J30").ClearContents
        .Range("B20").Value = 2014
        .Range("C20:J20").Value = 0
        For i = 3 To Target.Value * 2 Step 2
            .Range("B" & i + 19).Value = .Range("B" & (i + 19) - 2).Value + 1
            .Range("C" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("D" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("E" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("F" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("G" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("H" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("I" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
            .Range("J" & i + 19).FormulaR1C1 = "=R[-2]C*1.06"
        Next i
    End With
    Application.EnableEvents = True
End Sub

I just can't figure out how the code works to amend a formula and I would really like to get my head around it. The specific part that i am struggling with is:

.FormulaR1C1 = "=R[-2]C*1.06"</pre>

I appreciate your help and advice a lot!

 

[mabbutt]

I have made a little more progress and now I understand what the R1C1 means and what it references.

So now I have got the following code working:

.Range("C" & i + 19).FormulaR1C1 = "=R[-2]C*0.06+R[-2]C"

The only thing I have left to do is put a cell reference in instead of 0.06 so that the formula works when the value is changed. This is the part I am now stuck on!

So my question is how do I change 0.06 in the formula to reference cell D4?

I have tried using R4C4 but this isn't working.

Any help is appreciated

 

[Andrew Poulsom]

R4C4 is an absolute reference to D4, so it should work if that's what you want.

 

[mabbutt]

R4C4 is an absolute reference to D4, so it should work if that's what you want.

Hi Andrew, I closed Excel and opened it again and now it is working!

Thank you so much for helping me and teaching me how to do this

 

[Andrew Poulsom]

If the code errored at some point in your testing, events would have been disabled. Is that what you meant by not working - ie nothing happened? You can quickly reenable events by typing:

Application.EnableEvents = True

in the Immediate window and pressing Enter.

 

[mabbutt]

If the code errored at some point in your testing, events would have been disabled. Is that what you meant by not working - ie nothing happened? You can quickly reenable events by typing:

Application.EnableEvents = True

in the Immediate window and pressing Enter.

Yes that worked! Thank you again