User input controlled data

mabbutt

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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
82
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
 
Solution

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
82

ADVERTISEMENT

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
82
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

ADVERTISEMENT

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
82
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,051
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top