User input controlled data

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
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 :eek:

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

You can't have the formula:

=SUM(C1*0.06+C1),

in C1. That will create a circular reference.
 

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this in the module for the Worksheet:

Code:
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

Board Regular
Joined
Oct 4, 2014
Messages
71
Try this in the module for the Worksheet:

Code:
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:

Code:
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:

Code:
.FormulaR1C1 = "=R[-2]C*1.06"</pre>
I appreciate your help and advice a lot!
 

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
71
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:

Code:
.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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
R4C4 is an absolute reference to D4, so it should work if that's what you want.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 4, 2014
Messages
71
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 :)
 

Forum statistics

Threads
1,086,245
Messages
5,388,671
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top